Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Concept of Data and Index pages Expand / Collapse
Author
Message
Posted Friday, December 27, 2013 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:19 AM
Points: 49, Visits: 167
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.
Post #1526125
Posted Friday, December 27, 2013 7:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1526140
Posted Friday, December 27, 2013 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:19 AM
Points: 49, Visits: 167
Really helpful... Thanks
Post #1526144
Posted Friday, December 27, 2013 9:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:19 AM
Points: 49, Visits: 167
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
Post #1526262
Posted Saturday, December 28, 2013 2:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1526271
Posted Saturday, December 28, 2013 3:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1526280
Posted Saturday, December 28, 2013 11:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:19 AM
Points: 49, Visits: 167
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].
Post #1526325
Posted Sunday, December 29, 2013 3:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1526329
Posted Sunday, December 29, 2013 11:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526355
Posted Sunday, December 29, 2013 10:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 12:19 AM
Points: 49, Visits: 167
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 ,
Post #1526383
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse