Clustered Index Filegroup Location

  • jim5400

    SSC Enthusiast

    Points: 137

    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

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9486

    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 Ellis
    www.gethynellis.com

  • Michael Earl-395764

    SSC Guru

    Points: 53873

    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.

  • Jim H

    SSC Journeyman

    Points: 91

    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

  • DBA_Rob

    Default port

    Points: 1492

    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.

  • Paul White

    SSC Guru

    Points: 150341

    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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply