April 3, 2009 at 9:40 pm
Can we have a primary key on one column and clustered index on other column of the same table?
Is it possible to have a primary key on column A and clustered index on other column B of the same table?
April 3, 2009 at 9:50 pm
You are only allowed to have one clusted index on a table. By default, when you create a primary key, a clustered index is created on the columns that make up the primary key, but... A primary key does not have to have a clustered index. You can remove the clustered index from the primary key and make it a unique index and then add the clustered index on the other column.
April 3, 2009 at 10:05 pm
As Ken said, its not compulsory to have a clustered index to primary key... By default primary key will have clustered index. You can drop that clustered index and make other column as clustered index..
April 4, 2009 at 2:46 am
You can also create the primary key as a nonclustered index and then create the cluster elsewhere.
ALTER TABLE .. ADD CONSTRAINT ... PRIMARY KEY NONCLUSTERED
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2010 at 11:30 am
Just consider that the clustered index reorganizes the entire table so that the rest of the row content is physically on the same pages or nearby, in the order of the clustered index columns sort sequence. Therefore the clustered index should be intended to support the most intensively used sets of common key values whether its certain queries or update/insert transactions. Hopefully that's where you're headed with this. Sometimes the primary key while correct from an ER data modeling perspective does not really indicate the most intensively used access paths to the table that the application will exercise.
Another interesting option is if you need two different high performance paths to the data, is do the table itself to optimize for update/inserts to favor contiguous ranges of keys, however the app actually works, whether that means clustering the PK or the other key. Then if there is another access path that is mostly for intensive SELECT access, create a view of the columns needed to support that query (keys plus any other "covering index" data columns); then you "materialize" the new view by creating a clustered index on the view itself. The downside is that now the db engine must apply all inserts/updates/deletes to the alternative data structure too. If the table is super dynamic then this might be a bad idea.
We used that to implement a weird name search where primary, preferred, and previous names, with hyphenated names broken out three ways (Jones-Smith, Jones, and Smith) were all set up as nine views, with nine clustered indexes, and then a master view doing a UNION of the other nine views. Incredibly fast, the database engine does all the heavy lifting on the queries against the master search view. But names trickle in a few per minute to an existing 2.4 million row underlying table and the data is not terribly dynamic so updating the additional blocks of index content turns out to be acceptable overhead (some of the views only have a couple thousand rows in them, since the last name doesn't meet that view's criteria for Select). We also rebuild the indexes at night as needed using an automatic script written by MS or some MVP that has been floating around the DBA world for a while.
January 13, 2010 at 12:56 pm
Jason Pociask (1/13/2010)
Therefore the clustered index should be intended to support the most intensively used sets of common key values whether its certain queries or update/insert transactions.
That's one of the schools of thought for placement of the clustered index. It's not the only one though. http://www.sqlservercentral.com/articles/Indexing/68563/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2010 at 1:04 pm
The unchanging and ever-increasing attributes of the clustered index are certainly important considerations and are the ideal practical case, very true. Let's consider that amendment 1 to what I wrote then, good point.
If you can afford index rebuilds as needed and/or enough free space in pages to handle a significant percentage of dispersed or non-increasing inserts then you could consider some tradeoffs I suppose.
January 13, 2010 at 1:12 pm
Jason Pociask (1/13/2010)
If you can afford index rebuilds as needed and/or enough free space in pages to handle a significant percentage of dispersed or non-increasing inserts then you could consider some tradeoffs I suppose.
Or if it's a read-only environment. That article was written mostly from the point of OLTP systems. Data warehouses (especially the load daily types) are often indexed very differently
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply