Post reply

Introduction to Indexes

  • October 26, 2009 at 12:00 am

    #212882

    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

  • October 26, 2009 at 2:59 am

    #1070570

    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

  • October 26, 2009 at 3:40 am

    #1070580

    thanks Gail...

    waiting for ur next post ...

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

  • October 26, 2009 at 3:56 am

    #1070586

    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

  • October 26, 2009 at 6:13 am

    #1070635

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 26, 2009 at 7:02 am

    #1070658

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

    Rog

  • October 26, 2009 at 7:08 am

    #1070660

    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

  • October 26, 2009 at 7:15 am

    #1070667

    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.

  • October 26, 2009 at 7:24 am

    #1070679

    Excellent job, Gail!

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

  • October 26, 2009 at 8:48 am

    #1070736

    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. 🙂

  • October 26, 2009 at 8:50 am

    #1070739

    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.

  • October 26, 2009 at 8:55 am

    #1070745

    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

  • October 26, 2009 at 9:06 am

    #1070755

    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

  • October 26, 2009 at 9:13 am

    #1070760

    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

  • October 26, 2009 at 9:19 am

    #1070767

    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 124 total)

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