Index Architecture - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Index Architecture

Author

Topic

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-01-06 : 04:11:25

In SQL Server Each page size have a 8KB of Size,Indexes are Stored in page,Pages are formed B-Tree Structure.

B-Tree Structure Have a Three Level

One root node
Two or more branch nodes
Many leaf nodes

if Root pages will Reach 8KB the SQL Server will Create Another Root Node Or Branch node?

Kindly Clarify How Indexes Are Stored in SQL Server

--
Ragu Thangavel
Junior SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-06 : 05:10:10

That's not quite correct.

An index will have one root, 2 or more leaf pages and may have intermediate (what you've called branch) nodes. Indexes can easily be only 2 levels deep, in which case there's no intermediate nodes. When the index grows so that all the page references don't fit into the root page, that's when an intermediate level is created. There can and will only ever be one root node.

http://www.sqlservercentral.com/articles/Indexing/68439/

--
Gail Shaw
SQL Server MVP

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-01-06 : 06:38:24

Thank Gail Shaw

I red your writeup on SQL ServerCentral.com.
i have one clarification about root and intermediate Nodes.

Every Time root and intermediate page data ranges will change or not?

Ex

starting Time the root page have (1,10,20) index records.when root page reach his 8 KB size.it will re arrange the index records to 1,100,200 after 1,1000,2000

Kindly Clarify this

--
Ragu Thangavel
Junior SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-06 : 07:12:29

Don't understand what you're asking.

--
Gail Shaw
SQL Server MVP

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-01-06 : 07:31:38

Sorry i'm not fluent with English

[url]http://www.sqlservercentral.com/articles/Indexing/68439/[/url]
this Your writeup

the root page index ranges are in (1,10,20,38).
root node will grow up to 8KB then index range will change or not?

--
Ragu Thangavel
Junior SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-06 : 07:51:08

No, if that page fills up, then another page will be added at that level (say with 56, 78, 82, 100 on it), then another level added above (the new root page) which will have 1 and 56 in it (as the starting key for each page beneath it)

--
Gail Shaw
SQL Server MVP

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-01-07 : 00:32:25

Thank you very much for your replies.

Sorry for disturbing You

I'm so confused, till i'm not visualize,

what processes are involved while new records stored in index pages?
How SQL Server Organize the B-Tree Structure?

will you please explain how new records stored in B-Tree format with diagrammatic explanation ?

--
Ragu Thangavel
Junior SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-07 : 03:03:41

Honestly I don't have time to draw up a whole bunch of diagrams and I don't really understand what you're asking

New records are simply written to the page that they belong on (according to index key). If the page is full, a page-split occurs, a new page is created and the links within the other pages in the b-tree are changed to accommodate this new page.

--
Gail Shaw
SQL Server MVP

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-01-07 : 04:24:25

I am writing what i Understood.

1.In B-Tree only one Root Node

2.If root node page will reach 8KB then New Root page will create and old root page will change into intermediate page.

These are Correct or Wrong

--
Ragu Thangavel
Junior SQL Server DBA

raguyazhin
Posting Yak Master

105 Posts

Posted - 2011-01-07 : 04:37:27

[url]http://www.youtube.com/watch?v=coRJrcIYbF4[/url]

Is this B Tree structure video correct?

--
Ragu Thangavel
Junior SQL Server DBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-07 : 07:14:29

2) and another intermediate page will be created to hold the rows that couldn't fit onto the original root page. Plus whatever new pages will be getting added to the lower levels of the index.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources