Concept of Data and Index pages

  • [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]

  • 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
  • Really helpful... Thanks

  • 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

  • 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
  • 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]

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

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

  • 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 ,

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

  • This should help you to understand the page basics.

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

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

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