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

avatar image

562 29 31 36

(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

avatar image

Rob Farley
5.8k 16 22 28

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

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: 1983 times

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

Copyright 2018 Redgate Software. Privacy Policy