Introduction to Indexes

Question

Introduction to Indexes

Viewing 15 posts - 61 through 75 (of 124 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 2

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

bwilliams-1049831 Mr or Mrs. 500 Points: 532 More actions · Answer 3

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 6

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

ranjeet.techie Newbie Points: 3 More actions · Answer 7

Amazing article Gail .... This clarified a lot of things ... Thanks a lot ...

rafidheenm SSC Veteran Points: 240 More actions · Answer 8

rafidheenm

SSC Veteran

Points: 240

December 23, 2009 at 12:01 am

#1095808

Nice article..

Thanks gail...

sqlusers SSCrazy Points: 2463 More actions · Answer 9

Thanks Gail ...

Please elobrate more on Index Scan Properties. 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

What more are you looking for?

http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

http://sqlinthewild.co.za/index.php/2008/04/23/execution-plans-important-properties/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jungnaja SSC-Addicted Points: 422 More actions · Answer 11

jungnaja

SSC-Addicted

Points: 422

February 21, 2010 at 4:29 pm

#1122618

Love it! Thanks!

brendanc Old Hand Points: 356 More actions · Answer 12

Hi Gail

Just a comment, clustered indexes are order physically when the index is created and maintained logically there after. Kimberly trip and Paul Randall confirm this in the MCM video's. It would not make sense to just link pages logically because your disk would take massive strain when reading a range of data. This would also explain where all the disk space suddenly comes from when you build clustered indexes on tables that never had them before. Or if you have shrunk a database down to nothing and then rebuild indexes the space that you where trying to reclaim is now there again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

brendanc (1/6/2011)
Just a comment, clustered indexes are order physically when the index is created and maintained logically there after.

Physically within the file, yes (mostly), physically on disk still no guarantees, and nonclustered indexes are the same. They still don't enforce physical order, which is why I make a distinction.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

P Jones SSChampion Points: 12361 More actions · Answer 14

How did I miss these first time round? Really useful set of articles and I will be referring back to them, especially when trying to explain indexes to other developers.

Thanks Gail.

Albertus Dunn SSC Enthusiast Points: 184 More actions · Answer 15

Thanks Gail. Brilliant article. Can't wait for part 2 and 3.