Post reply

What is an index.is is object ??how visualize...

  • April 8, 2013 at 9:33 pm

    #276251

    Hi we can visualize a table as m*n matrix where m and n indicates number of rows and columns . But how index can be visualize.is it same as that of index on books.Table are allocated extents,then why why index are allocated pages . can index size becomes soo huge that they have to be allocated extents?

    plz help or provide necessary links

  • April 8, 2013 at 10:25 pm

    #1604655

    But how index can be visualize.is it same as that of index on books.

    Pretty much. Non-filtered indexes will have an entry for every row in the table, sorted per the index key like the index in a book. Each entry in the index has information that leads back to the row in the table that it represents in case a query that uses the index needs more data than in stored in the index to satisfy the rest of the query.

    Table are allocated extents,then why why index are allocated pages .

    Think of both index and table data as being stored on pages. Extents are something the storage engine deals in as an allocation optimization but is not usually important when thinking about the logical workings of an index, except maybe in the case of heaps.

    If you're really interested in the inner workings get a copy of one of Kalen Delaney's books with "inside sql server" or "sql server internals" in the title.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 9, 2013 at 1:39 am

    #1604706

    anoop.mig29 (4/8/2013)

    Table are allocated extents,then why why index are allocated pages . can index size becomes soo huge that they have to be allocated extents?

    Tables and indexes are both allocated pages, a set of 8 contiguous pages aligned on a 64k boundary is called an extent. It's not a special structure, just 8 pages. The allocations to both indexes and tables are pages.

    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

Viewing 3 posts - 1 through 2 (of 2 total)

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