Hi I read the functionality of non-clustured index in a post,
A non clustered index key value is used to point to data rows that contain the key value. This value is known as row locator. The structure of the row locator is determined on the basis of the type of storage of the data pages.If the data page is stored as a heap, a row locator becomes a pointer to a row. If the data page is stored in a clustered table the row locator is a clustered index key. Clustered and Non clustered indexes can be unique and indexes are automatically maintained for a table or view whenever the data table is modified.
My question,what is data stored as heap here? Is there any more diffrent data storages and functionality changes with that for indexes? I know SQL server for last 3 years,but still i have millions of doubts on indexes.Your kind answers will helps me better,thanks in advance..
asked Mar 22, 2010 at 02:34 AM in Default
I can recommend you read Kimberly Tripp's chapter on indexes in the book "SQL Server 2008 Internals" by Kalen Delaney. Read it over and over.
Table data is stored either as a heap or as a b-tree. If you don't have a Clustered Index, it's a heap, otherwise it's a b-tree based on the key columns in your Clustered Index (plus a uniquifier if it's not unique).
So a nonclustered index is a copy of some of the data, with information at the leaf of the b-tree to the CIX key or Heap RowID.
answered Mar 22, 2010 at 04:00 AM