June 24, 2008 at 8:54 pm
Hi All,
I am creating a partition scheme for a table that I am spreading across three filegroups. When I ran my script to create the table and associate it with the partition scheme, I got the warning:
The filegroup 'PRIMARY' specified for the clustered index 'PK_ProductData' was used for table 'dbo.kk_ProductData' even though partition scheme 'ps_Categories' is specified for it.
So basically my table is being created in one filegroup (actually 3) and my index is being created in a completely different filegroup. What is the best rule of thumb in this situation? Is it best to place the clustered index on one of the three file groups that I created for the partitioning scheme or is it a best practice to actually keep the index in the PRIMARY file group, which is where all of my non-partitioned tables reside?
Thanks,
Jim
June 25, 2008 at 4:01 am
I don't think its possible to split the clutered index from the table as the clustered index represents the physical ordering of the data in that table.
Gethyn Elliswww.gethynellis.com
June 25, 2008 at 8:06 am
The clustered index is the actual table data. So, by specifying a file group for the clustered index, you have changed your partitoning scheme to put everything on the primary file group. That is what the message is telling you - the partitions specified in the partitioning scheme are being ignored and all of your data is going into a single file group.
June 26, 2008 at 9:29 am
Hi SSCommitted, I actually disagree with your response. I INSERTED data into the partitioned table and after selecting from each of the partitions, I can see that the records are going to the expected partitions even though the clustered index resides on the PRIMARY filegroup as the error suggests
So, I am a little confused here. Based on your reply, does that mean you should not have a clustered index on a partitioned table? If you are partitioning a table across three different filegroups and you can only choose one filegroup for the clustered index, then what are you supposed to do? Does that mean if instead of choosing PRIMARY, I chose to put the clustered index on one of the filegroups that I used for the partitioning that all of the data would go there and the other two filegroups would be ignored? Even though the clustered index is on a different filegroup than the table data, the partition scheme still seems to work so I am trying to understand the consequences of having it configured that way.
Perhaps the error is saying that it ignored my request to place the clustered index on the PRIMARY filegroup and instead spread it across my three filegroups that I am using to partition.
Jim
June 22, 2009 at 3:19 pm
I realize this is an old thread, but I found it while investigating partitioning a table. From what I have found, Michael Earl is correct. The fact that you specified the PRIMARY filegroup for your clustered index did indeed move the entire table to the PRIMARY filegroup. It still does partition the data, it just doesn't put the data where you thought it would go. In fact, if you tried to delete the filegroups that were defined in your pratitioning scheme, it would say they were in use; but not because there was data inthem, only because the scheme still references them.
To answer your other question about whether or not a partitioned table should have a clustered index, I think the answer is yes. The answer certainly is that a clustered index is allowed. I found the following in BOL in a section titled "Special Guidelines for Partitioned Indexes":
Partitioning Clustered Indexes
When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL Server adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column.
June 24, 2009 at 9:36 pm
Just in case there is any doubt at all about this: If you specify a file group for a clustered index (primary key or unique constraint) in a CREATE TABLE statement, and you also specify that the table should be created on a partition scheme, SQL Server honours the constraint - the partitioning scheme is ignored.
Books Online : CREATE TABLE
If the PRIMARY KEY or UNIQUE constraint creates a clustered index, the data pages for the table are stored in the same filegroup as the index. If CLUSTERED is specified or the constraint otherwise creates a clustered index, and a is specified that differs from the or filegroup of the table definition, or vice-versa, only the constraint definition will be honored, and the other will be ignored.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply