We have a large table ~40 Million rows and about 35 columns. Things are starting to slow down and i am looking at partioning. The table has some historical data and most of it can be ignored except for the current year, so I wanted to partion
the data on the year column. So I created a filegroup and ndf/ldf files for each year (ie: 2005,2006,2007, etc...)
The primary key is clustered on an identity column in this table. When I went to recreate the table I got a warning message that the clustered index would be created on the primary file group as I specified, so i decided to do some research and found
This leads me to believe that creating a clustered idex on the primary file group will prevent true partionting. Is this true? If so where should I store my clustered index as this seems to defeat the purpose of partioning then.
Also I was reading something that the clustered index should contain the key column that the data is partioned on, so it should be something like (PriKeyCol, theYear) ? as opposed to just the (PriKeyCol)
Thanks for any help,
View Complete Post