Concept of Data and Index pages

Question

Concept of Data and Index pages

sql crazy kid

Ten Centuries

Points: 1388
More actions
December 27, 2013 at 6:55 am

#301651

[font="Tahoma"]Data pages stores data other than text,varchar(max),Image..etc .
Index page stores index entries.
If i created a clustered index ,that physically sorted as the actual data available on the leaf node.
When i query data based on my clustered key,does this get data from index page,or from the data page?
If this from Index page, same data stored in data page as well?
Please help me to understand the concept of Data and Index Pages.;-)[/font]

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

The leaf level of the clustered index are data pages (and are not necessarily stored in physical order). The non-leaf levels of the clustered index are index pages. Leaf and non-leaf levels of nonclustered indexes are index pages

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

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 2

sql crazy kid

Ten Centuries

Points: 1388

December 27, 2013 at 7:43 am

#1676082

Really helpful... Thanks

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 3

Clustered index always seen sorted table in physical order, As per the previous this is not necessarily stored in physical order,Is there any specific case clustered index not sorted in physical order?

"The leaf level of the clustered index are data pages (and are not necessarily stored in physical order)."

Thanks in advance

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

sql crazy kid (12/27/2013)
Is there any specific case clustered index not sorted in physical order?

No specific case. It's the norm in fact.

Don't mistake query results order (which results from logical storage order and query processor behaviour) with physical storage order.

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

LutzM SSC Guru Points: 107049 More actions · Answer 5

I recently came across an index defintion I thought to be inefficient:

CREATE TABLE myTable (myId INT IDENTITY(1,1))

CREATE CLUSTERED INDEX weird_index ON myTable(myId DESC)

The reson for being inefficient from my point of view:

The values of myId are increasing but the sort order of the clustered index is descending.

Wouldn't this lead to a high frequence of page splits since the next identity value would be physically stored "before" the previous value?

Or am I missing something here?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 6

Kindly give some more ideas about Internal architecture of Non Leaf nodes.

As per my understanding this having page header,index rows which helps to find next Non Leaf node/Leaf node.

Correct me if i am wrong .

How this finding works with page header and index rows.

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

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

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

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

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

As well as the marvelous articles that Gail posted, Books Online has some great information in it (if you don't know what Books Online is, post back... you cannot live without it) under an index search of "index architecture [SQL Server]".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 9

Jeff Moden (12/29/2013)
As well as the marvelous articles that Gail posted, Books Online has some great information in it (if you don't know what Books Online is, post back... you cannot live without it) under an index search of "index architecture [SQL Server]".

Hello Jeff,

Kindly post the link of Books Online . That would be additional for me to which Gail has posted ,

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

sql crazy kid (12/29/2013)
Jeff Moden (12/29/2013)
As well as the marvelous articles that Gail posted, Books Online has some great information in it (if you don't know what Books Online is, post back... you cannot live without it) under an index search of "index architecture [SQL Server]".
Hello Jeff,
Kindly post the link of Books Online . That would be additional for me to which Gail has posted ,

"Books Online" is the help documentation system that comes with SQL Server. You can get there by opening SSMS and then pressing the {F1} key. If you didn't install "Books Online" locally on the server, I believe it'll take you to the near equivalent on the Microsoft site auto-magically.

As a bit of a sidebar, if you Google for "what is books online sql", it'll lead you to various MS sites that either are "Books Online" for the various versions of SQL Server, or sites that you can download it from. I prefer a local copy on my desktop so that I can mark "favorites" in a dedicated fashion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rahul Bhosale SSC Eights! Points: 931 More actions · Answer 11

This should help you to understand the page basics.

How SQL Server stored rows in pages...[/url]