Stairway to SQL Server Indexes: Step 1, Introduction to Indexes

  • David Durant

    Ten Centuries

    Points: 1370

    Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 1, Introduction to Indexes

  • Anipaul

    SSC-Insane

    Points: 24681

    Great article...Well written.

  • Gary Noter

    SSC-Addicted

    Points: 495

    Well written; excellent analogies.

    I've known of indexes for quite some time (nearly 20 years; oh I'm getting old[er]!) and have implemented and used them countless times.

    I'm confident I implement indexes reasonable enough (:Whistling:), though will definitely benefit becoming more technically knowledgeable of them.

    Starting at Level 1 and moving forward, all the while with the mindset I'm 'learning' something new, will reintro and expand my skill set on the topic.

    Thx.

  • genger_stiedle

    Valued Member

    Points: 65

    Thank you for your article.

    I would appreciate some information on how to choose a clustered index and constraints.

    I have read articles about hotspots and not choosing a incrementing id and then other articles that say this was meant for 6.5. So please explain 🙂 I really don't have to deal with transactional data even for my data tables so I'm not sure that hotspots are something I need to worry about.

    I created a clustered index on the id and set is as my pk and a non clustered unique constraint on several columns that by definition have to be unique. I have read some things by Celko that I believe says incrementing fields are extraneous and he would prefer just using the compound pk. But, I like my id for foreign keys.

    I also read that size matters and if it's a small table a full table scan takes place anyway. My look up tables are small but my data tables house millions of records.

  • a.rajmane

    Old Hand

    Points: 377

    gr8, well written

    www.sqlsuperfast.com

  • Jankhana

    SSChasing Mays

    Points: 621

    Great article. Very apt 🙂 Waiting for the other all sequences 🙂

  • Slick84

    SSCertifiable

    Points: 5602

    Thanks, I think this is the first index article genuinely written with a n00b in mind.

    I'm waiting for your next StairWay. Any idea when we should expect that?

    --
    :hehe:

  • Gary Noter

    SSC-Addicted

    Points: 495

    Do a search on SqlServerCentral for:

    Stairway to SQL Server Indexes

    There seem to be 7 Steps so far.

    Also, consider checking out this entire "Stairway" series at:

    Stairway Series[/url]

    (http://www.sqlservercentral.com/stairway)

  • Slick84

    SSCertifiable

    Points: 5602

    Oh nice! Thanks for the link.

    --
    :hehe:

  • David Durant

    Ten Centuries

    Points: 1370

    Gary,

    There will be 15 levels (articles) in the indexing Stairway. I have delivered all 15 to the editor, Kalen Delaney, and I believe she has passed them all on to Redgate. There is always some delay while Redgate proof reads, adds graphics etc. But, I would expect to seem them all on the Stairway soon.

    Dave Durant,

    Author.

  • X X-488698

    Grasshopper

    Points: 12

    I've been working with databases for about 15 years now, though more as a developer than a dba, and this is the first article I've read that explained indexing in a way that I understand. Well done!

  • Peter Maloof

    SSCommitted

    Points: 1733

    David:

    I have to disagree that a phone book is an unclustered index.

    Unless I'm mistaken, the white pages contain data physically sorted by

    last name, first name and address. Once you access the entry

    you're looking for, you have all the data; there's no bookmark to follow.

    Maybe I'm misunderstanding something, but that sounds like a clustered index to me.

    Thanks,

    Peter


    Peter MaloofServing Data

  • jpomfret7

    SSCommitted

    Points: 1535

    Peter Maloof (7/5/2011)


    David:

    I have to disagree that a phone book is an unclustered index.

    Unless I'm mistaken, the white pages contain data physically sorted by

    last name, first name and address. Once you access the entry

    you're looking for, you have all the data; there's no bookmark to follow.

    Maybe I'm misunderstanding something, but that sounds like a clustered index to me.

    Thanks,

    Peter

    Peter,

    I think the information we are looking for is the hat size rather than the phone number. Once we use the index to get the phone number we then have to use the phone number to get the information. He mentioned the physical houses are not in order which is why it wouldn't be clustered.

    Jess

  • Peter Maloof

    SSCommitted

    Points: 1733

    Jess:

    You're right. Phone books are used to demonstrate clustered indexes, so I missed the next step of getting hat sizes.

    Thanks for pointing that out.

    Peter


    Peter MaloofServing Data

  • whattheETL

    SSC Enthusiast

    Points: 161

    This is an excellent article that takes the abstract concept of a nonclustered index and explains it in a simple everyday example. Well done. I look forward to reading the next 14 articles this week.

    Thank you.

Viewing 15 posts - 1 through 15 (of 28 total)

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