|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 1,775,
Visits: 2,432
|
|
GilaMonster (11/19/2009)
Do you have queries that filter on the combination of SSN + MyDataID or City + MyDataID?
p.s. That's a poor choice for a clustered index. See part 2 of this indexing series.
Not sure you can make that statement without knowing the data access patterns of the system. Yes it carries overhead on NC indexes and can have fragmentation issues, etc, but if the vast majority of their queries could benefit from a CI on LastName then it could be optimal despite some obvious drawbacks.
Best,
TheSQLGuru
SQL Server MVP
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 105,
Visits: 143
|
|
Gail,
I'm pretty sure you haven't answered my question.
The question is, not is it necessary to have the primary key specified in indexes, but is it redundant?
Doug
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, December 29, 2009 11:57 AM
Points: 24,
Visits: 66
|
|
I believe the primary key is redundant in those indexes.
If you were to join tables using this primary key with a where clause using the lastname I would expect the optimizer to use the internal clustered key from the primary key. not the primary key field in the second column of your index.
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 1,775,
Visits: 2,432
|
|
Douglas Osborne-456728 (11/19/2009)
Gail,
I'm pretty sure you haven't answered my question.
The question is, not is it necessary to have the primary key specified in indexes, but is it redundant?
Doug
My vote (although I am not certain and don't have time to gen up a test) is that it is not redundant to include the PK CI column as a column in a NC index. I think if you have NC index on lastname, PKID you can get a seek without lookup on a select lastname, PKID where clause of lastname = 'asdf' and PKID between 1 and 5 but you would not get a seek on same if you just had index of lastname without the PKID. Something is just tickling the back of my memory that I have run into that exact same situation before and went "hmm, I didn't expect that behavior"...
Best,
TheSQLGuru
SQL Server MVP
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 16,983,
Visits: 12,108
|
|
Douglas Osborne-456728 (11/19/2009)
The question is, not is it necessary to have the primary key specified in indexes, but is it redundant?
I'm obviously explaining badly.
What columns the primary key is on has absolutely nothing to do with redundant columns in indexes. What can be redundant when specified in indexes is the clustered index key (since it's present anyway). A primary key is not always clustered (though lots of people do think Primary Key = Clustered Index). In fact, in your case it is not.
Gail Shaw
We walk in the dark places no others will enter
We stand on the bridge and none may pass
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 105,
Visits: 143
|
|
Gail,
Now I've got - only if the primary key is clustered is it present in all of the other keys.
I won't even tell you how many of the PKs here are Clustered Primary Key GUIDs - lol.
Thanks for taking your valuable time to beat this into my head.
Doug
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, March 03, 2010 12:36 PM
Points: 1,
Visits: 26
|
|
| Amazing article Gail .... This clarified a lot of things ... Thanks a lot ...
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, January 06, 2010 1:36 AM
Points: 6,
Visits: 22
|
|
Nice article..
Thanks gail...
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Friday, March 05, 2010 2:32 AM
Points: 51,
Visits: 141
|
|
Thanks Gail ...
Please elobrate more on Index Scan Properties. 
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 16,983,
Visits: 12,108
|
|
|
|
|
