SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Clustered Index Filegroup Location


Clustered Index Filegroup Location

Author
Message
jim5400
jim5400
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 6
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:

[color=#FF0000]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.[/color]

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
Gethyn Ellis
Gethyn Ellis
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8449 Visits: 2949
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
Michael Earl-395764
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47875 Visits: 23078
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
Jim H
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 15
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
DBA_Rob
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1361 Visits: 386
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
Paul White
SSC Guru
SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)SSC Guru (132K reputation)

Group: General Forum Members
Points: 132039 Visits: 11445
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search