question

venkatreddy avatar image
venkatreddy asked

Non clustured index functionality...need clarifications

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

t-sqlindexingsubjective
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Rob Farley avatar image
Rob Farley answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.