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

Question

Post reply

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

ravi@sql

SSCrazy

Points: 2121
More actions
August 8, 2016 at 2:54 am

#328968

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:)

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

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

koteravindra SSC Rookie Points: 37 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

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