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

more ▼

asked Mar 22, 2010 at 02:34 AM in Default

venkatreddy gravatar image

562 28 29 32

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 22, 2010 at 04:00 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

thanks a lot Rob,so its a b-tree structure in the presence of clustured index and heap in the absence of clustured index.let me go indepth and will be back with few more if any.I will bag the book that you suggested asap.
Mar 22, 2010 at 04:20 AM venkatreddy
Excellent book. Total agreement.
Mar 22, 2010 at 09:07 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 22, 2010 at 02:34 AM

Seen: 1418 times

Last Updated: Mar 22, 2010 at 02:34 AM