October 25, 2006 at 9:22 am
Hello,
Is it true that if I have a unique clustered index on one or more table fields, and have a Primary Key constraint on those same fields, that I can have the index reside on a seperate physical disk?
If you know of resources on this topic you could recommend, please share, and please let me know whether or not this can be done. It seems like I read that this could be done, but maybe not.
Thank you for your help!
CSDunn
October 25, 2006 at 9:56 am
What is the purpose of this. The data is always stored with the clustered index. Any subsequent index can be stored elsewhere on disk and will only contain the key values and the clustered index values(for pointer lookup). By creating a unique clustered index and a primary key (unique) you are duplicating your efforts. It will have to check and organize the primary key constraint along with check and organize the clustered unique index. Two times overhead for no gain what so ever.
Tom
October 25, 2006 at 10:46 am
It is only possible if your PK constraint is NON-clustered. If your PK is clustered, then it and the clustered index will be one and the same thing.
I'm also assuming that you are specifying the key columns in the same order (your question doesn't actually state this). If not, then these are two different indexes and it is most certainly possible. You would need to create a new filegroup in the database, and then specify that filegroup as part of the CREATE INDEX command.
Depending upon which columns were referenced in your Search Argument in the WHERE clause, one index might be preferred over the other.
I can see there might be other situations - the non-clustered PK could act as a covering index under some circumstances, and if the only columns you needed to return for a particular query were some or all of the key columns, then the NC index would be used in preference to the clustered index as it would be narrower.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply