August 8, 2016 at 2:54 am
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:)
August 8, 2016 at 5:54 am
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
August 8, 2016 at 6:47 am
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
August 8, 2016 at 8:49 am
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
August 8, 2016 at 11:00 am
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
August 8, 2016 at 11:46 am
Lynn Pettis (8/8/2016)
The Dixie Flatline (8/8/2016)
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspxMake 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
August 8, 2016 at 10:59 pm
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
August 9, 2016 at 4:44 am
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
August 9, 2016 at 11:20 am
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