Clustered and Non-Clustered Index

Non-Clustered Index
Creating a Table
To better explain SQL Server non-clustered indexes; let’s start by creating a new table and populating it with some sample data using the following scripts. I assume you have a database you can use for this. If not, you will want to create one for these examples.
Create Table DummyTable1
(
EmpId Int,
EmpName Varchar(8000)
)
When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. We can confirm the fact that this new table doesn’t have an index by taking a look at the sysindexes system table, which contains one for this table with an of indid = 0. The sysindexes table, which exists in every database, tracks table and index information. “Indid” refers to Index ID, and is used to identify indexes. An indid of 0 means that a table does not have an index, and is stored by SQL Server as a heap.
Now let’s add a few records in this table using this script:
Insert Into DummyTable1 Values (4, Replicate ('d',2000))
GO
Insert Into DummyTable1 Values (6, Replicate ('f',2000))
GO
Insert Into DummyTable1 Values (1, Replicate ('a',2000))
GO
Insert Into DummyTable1 Values (3, Replicate ('c',2000))
GO
Now, let’s view the contests of the table by executing the following command in Query Analyzer for our new table.
Select EmpID From DummyTable1
GO
Empid
4
6
1
3
As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.
Now, let’s execute the following commands to display the actual page information for the table we created and is now stored in SQL Server.
dbcc ind(dbid, tabid, -1) – This is an undocumented command.
DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO
This script will display many columns, but we are only interested in three of them, as shown below.
PagePID IndexID PageType
26408 0 10
26255 0 1
26409 0 1
Here’s what the information displayed means:
PagePID is the physical page numbers used to store the table. In this case, three pages are currently used to store the data.
IndexID is the type of index,
Where:
0 – Datapage
1 – Clustered Index
2 – Greater and equal to 2 is an Index page (Non-Clustered Index and ordinary index),
PageType tells you what kind of data is stored in each database,
Where:
10 – IAM (Index Allocation MAP)
1 – Datapage
2 – Index page
Now, let us execute DBCC PAGE command. This is an undocumented command.
DBCC page(dbid, fileno, pageno, option)
Where:
dbid = database id.
Fileno = fileno of the page. Usually it will be 1, unless we use more than one file for a database.
Pageno = we can take the output of the dbcc ind page no.
Option = it can be 0, 1, 2, 3. I use 3 to get a display of the data. You can try yourself for the other options.
Run this script to execute the command:
DBCC TRACEON (3604)
GO
DBCC page(@DBID, 1, 26408, 3)
GO
The output will be page allocation details.
DBCC TRACEON (3604)
GO
dbcc page(@DBID, 1, 26255, 3)
GO
The data will be displayed in the order it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 both display the data page.
I have displayed the data page information for page 26255 only. This is how MS SQL stores the contents in data pages as such column name with its respective value.
Record Type = PRIMARY_RECORD
EmpId = 4
EmpName = ddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
Record Type = PRIMARY_RECORD
EmpId = 6
EmpName = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
Record Type = PRIMARY_RECORD
EmpId = 1
EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
This displays the exact data storage in SQL, without any index on table. Now, let’s go and create a unique non-clustered index on the EmpID column.

Ref: http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx