Introduction to Indexes

  • Comments posted to this topic are about the item Introduction to Indexes

    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
  • Amazing Gail...

    I have been looking for an article on Indexes as simple and exhaustive as this for long.

    Looking forward to the next 2 parts.

    Thanks

  • thanks Gail...

    waiting for ur next post ...

    please elaborate on the clustered & non clustered indexes & implementation of it in actual db

  • omkarred (10/26/2009)


    please elaborate on the clustered & non clustered indexes & implementation of it in actual db

    That's what parts 2 and 3 cover.

    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
  • Excellent introduction. Nice job Gail. Couldn't ask for better.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Thankyou for this, the best I have read so far, all makes sense, looking forward to the next parts

    Rog

  • Truly a case where pictures are worth thousands of words. Nicely done.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks Gail for mentioning that "When an index scan is done on the clustered index, it’s a table scan in all but name." Many folks think that performance is always better with clustered index scan than a table scan.

  • Excellent job, Gail!

    We're publishing part 2 in a couple weeks and part 3 as soon as possible after that.

  • This article is fortuitous for me since I attended a SQL 2008 Development class on Saturday and this topic was covered most of the afternoon.

    Thanks for the review. 🙂

  • Thanks for the article, Gail.

    "When an index scan is done on the clustered index, it’s a table scan in all but name."

    Then why do we see index scan and table scan separately in execution plan rather just table scan on a single query with simple join?

    Would wait for part 2 and 3.

    SQL DBA.

  • SanjayAttray (10/26/2009)


    Thanks for the article, Gail.

    "When an index scan is done on the clustered index, it’s a table scan in all but name."

    Then why do we see index scan and table scan separately in execution plan rather just table scan on a single query with simple join?

    Would wait for part 2 and 3.

    I don't follow that one Sanjay. Can you post a plan with what you are meaning and what the issue with that plan is?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Thanks Gail! This has helped my understanding on this topic. I have posted the article on an internal wikipedia as my Manager was very interested in the topic as well. This was very well written and I look forward to the next parts.

    Thanks again!

    Rob

  • SanjayAttray (10/26/2009)


    "When an index scan is done on the clustered index, it’s a table scan in all but name."

    Then why do we see index scan and table scan separately in execution plan rather just table scan on a single query with simple join?

    Possibly because they're against different indexes or different tables?

    What I'm saying there is that a clustered index scan is virtually the same as a table scan. You won't see the table scan operation against a table that has a clustered index. It appears in the execution plan as a clustered index scan. That doesn't make it any more efficient than a table scan.

    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
  • Excellent article. This coupled with Brad's article is very helpful in design considerations. I was especially looking for the size of the row pointer. It's quite a consideration for the choice of a clustered key. For example If I don't have a clustered index I get 8 byte pointers. If I used an INTEGER row ID (auto number) I get 4 byte pointers. If I use a US phone number as my clustered key I get 10 byte pointers. (I use data compression techniques).

    You said:

    Sure, hard drives are cheap and storage is abundant but increasing the size of a database has other effects,

    But that is not so true for those of us working on the Mobile. Every byte is precious.

    I'm looking forward to the other installments.

    ATBCharles Kincaid

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

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