SQLINFO – We play with data and technology

I hope everyone knows , in Sql Server the data are stored in the data pages.

HEAP:

A table with no clustered index is called a heap.

Consider a table ‘test’ which contain two columns ID, Name which doesn’t contain a Clustered index. So the data stored in the data page is same as we see in the table.

TABLE

ID	NAME
4	A
5	B
3	C
1	D

Data Page

ID	NAME
4	A
5	B
3	C
1	D

Clustered Index:

If we put a clustered index on the column ID i.e. here Primary key, then the data stored in the data pages in a sorted order of the column specified. The clustered index are organized as

B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. The leaf node contains the data pages.

TABLE

ID(PK)	NAME
4	A
5	B
3	C
1	D

Data Page

In data page the data is stores as follows,

ID(PK)	NAME
1	D
3	C
4	A
5	B

Non-Clustered Index:

Non clustered index also organized as the B-tree Structure.

• The data rows of the underlying table are not sorted and stored in order based on their non-clustered keys.
• The leaf layer of a non-clustered index is made up of index pages instead of data pages.
• If the table is heap, the row locater is a pointer to the row.
• If the table is having the clustered index ,the row-locater is the clustered index key.

Reference & Images: http://msdn.microsoft.com/en-us/library/ms177443.aspx

If you like this post Please make a comment.