Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
blackjackIT
Starting Member
25 Posts |
 Posted - 2007-07-20 : 14:57:14
|
| I have a set of tables with about the same structuredataID, recordID, 15 other columnsdataID is unique but is never referenced in queriesrecordID is one of the most referenced columns but only has a cardinality of about 30%The current structure has a clustered PK on (dataID,recordID)Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance anyAfter staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.create a non-clustered PK on dataIDcreate a non-unique clustered index on recordIDLet me know if any other information is needed. Thanks |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-20 : 15:10:42
|
| That sounds good.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-20 : 17:33:13
|
| That doesn't make much sense to me. I would not expect any improvement there.1) If dataID is never referenced, why create a non clustered index on it? Does it not even appear in joins btw?2) Non-unique clustered indexes are rarely advised. They end up with a "uniqifier" added to them which is then included in the index pointers of non clustered indexes. This index would be less efficient and possibly less effective than the clustered recordID, dataID index.Is it possible to post a query that you are having trouble with for comment? I like the suggestion you were given and am surprised, given the info, that there was no performance change. Did you (or do you know how to) check the execution plans? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-20 : 18:27:59
|
| Actually - as well as the query please could you post the entire table(s) DDL too? |
|
|
|
blackjackIT
Starting Member
25 Posts |
Posted - 2007-07-20 : 20:26:41
|
| 1) the non-clustered index is created automatically when I create the PK constraint. It's the PK because it's the only unique column in the table.2) I only created a non-unique clustered index because that column is the most referenced column and also has the greatest cardinality 2nd to the PK.I will post queries and DDL on Monday as I am at home at the moment. Have a great weekend all! |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-21 : 04:47:19
|
quote:
Originally posted by blackjackIT
1) the non-clustered index is created automatically when I create the PK constraint. It's the PK because it's the only unique column in the table.
Ah - my mistake. I didn't notice that the original pk of (dataID, recordID) was a superkey. In that event there is much of a muchness between the second structure and the third but certainly it makes more sense to me now. |
|
|
|
|
|
|
Indexing Suggestions