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

What is an index.is is object ??how visualize... Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 9:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 26, 2014 1:18 AM
Points: 90, Visits: 135
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
Post #1440126
Posted Monday, April 8, 2013 10:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1440135
Posted Tuesday, April 9, 2013 1:39 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 @ 12:33 PM
Points: 42,818, Visits: 35,943
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 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 #1440197
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse