Post reply

Introduction to Indexes

  • November 19, 2009 at 1:41 pm

    #1081825

    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

  • November 19, 2009 at 1:41 pm

    #1081826

    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

  • November 19, 2009 at 1:49 pm

    #1081833

    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.

  • November 19, 2009 at 2:17 pm

    #1081853

    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

  • November 20, 2009 at 1:39 am

    #1082019

    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

  • November 20, 2009 at 6:44 am

    #1082170

    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

  • December 8, 2009 at 1:45 pm

    #1089497

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

  • December 23, 2009 at 12:01 am

    #1095808

    Nice article..

    Thanks gail...

  • February 5, 2010 at 12:36 am

    #1114210

    Thanks Gail ...

    Please elobrate more on Index Scan Properties. 🙂

  • February 5, 2010 at 4:03 am

    #1114301

    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

  • February 21, 2010 at 4:29 pm

    #1122618

    Love it! Thanks!

  • January 6, 2011 at 10:25 pm

    #1270409

    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.

  • January 7, 2011 at 12:35 am

    #1270458

    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

  • January 7, 2011 at 1:17 am

    #1270479

    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.

  • January 7, 2011 at 2:00 am

    #1270490

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

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

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