explain composite index structure and work? How B Tree for composite index work internally?

  • HI Champs,

    explain composite index structure and work in sql server ? How B Tree for composite index work internally?

    i need to understand strcuturre of composite index , how value get calculated or value get stored internally and how it search ?

    Thanks in Advance

    Ravi:)

  • https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • And this[/url].

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • By 'composite', I assume you mean a multi-column index.

    If so, there's no calculations and the values are stored just as they would be with a single column index.

    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
  • The Dixie Flatline (8/8/2016)


    https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    Make it easier:

    https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

  • Lynn Pettis (8/8/2016)


    The Dixie Flatline (8/8/2016)


    https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    Make it easier:

    https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

    ๐Ÿ˜›

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • HI,

    suppose if I have 3 columns in index with index order as ASC .

    Emplid,deptid,Accountid

    10 2 3

    10 1 5

    So those values will store in order like

    10 1 5

    10 2 3

    in leaf node ?

    correct me If am wrong.

    Regards,

    Ravi@sql

  • Pretty much, yes.

    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
  • koteravindra (8/8/2016)


    HI,

    suppose if I have 3 columns in index with index order as ASC .

    Emplid,deptid,Accountid

    10 2 3

    10 1 5

    So those values will store in order like

    10 1 5

    10 2 3

    in leaf node ?

    correct me If am wrong.

    Regards,

    Ravi@sql

    At noted, basically yes, especially after a rebuild. But technically the rows within a page don't have to be in order, nor do the individual pages. But SQL guarantees that it can read them in order. It does that using pointers. The overhead of any other method would just be too much.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 9 posts - 1 through 8 (of 8 total)

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