SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concept of Data and Index pages


Concept of Data and Index pages

Author
Message
sql crazy kid
sql crazy kid
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 243
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.;-)

GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233829 Visits: 46362
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
sql crazy kid
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 243
Really helpful... Thanks
sql crazy kid
sql crazy kid
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 243
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233829 Visits: 46362
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
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24609 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
sql crazy kid
sql crazy kid
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 243
Kindly give some more ideas about Internal architecture of Non Leaf nodes.
As per my understanding this having page header,index rows[key and pointer] 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[key and pointer].
GilaMonster
GilaMonster
SSC Guru
SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)SSC Guru (233K reputation)

Group: General Forum Members
Points: 233829 Visits: 46362
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
Jeff Moden
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222728 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sql crazy kid
sql crazy kid
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 243
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 ,
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search