Introduction to Indexing Part 2

Creating a Non-Clustered Index

Now, we will create a unique non-clustered index on the empid column to see how it affects the data, and how the data is stored in SQL Server.

CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_empid
ON DummyTable1 (empid)
GO

Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1
26411	2	10
26410	2	2

Now, we see two more rows than before, which now contains index page details. Page 26408 displays the page allocation details, and pages 26255 and 26409 display the data page details, as before.

In regard to the new pages, page 26411 displays the page allocation details of an index page and page 26410 displays the index page details.

MS SQL generates a page (pagetype = 10) for an index and explains the page allocation details for an index. It shows the number of index page have been occupied for an index.

Let us see what would be the output for page 26411, that is page type = 10

IAM: Single Page Allocations @0x308A608E

-----------------------------------------

Slot 0 = (1:26410)

Let us view page 26410 to see the index page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26410, 3)
GO

SQL populates the index column data in order. The last column (?) is pointed to the row locator.

Here are the results, using two different methods:

Method I

FileID	PageID	EMPID	?
1	26410	1	0x8F66000001000200
1	26410	3	0x2967000001000000
1	26410	4	0x8F66000001000000
1	26410	6	0x8F66000001000100

The row location display in one of two ways:

·     If the table does not have a clustered index, the row locator will be combination of fileno, pageno and the no of rows in a page. 

·     If the table does have clustered index, the row location will be clustered index key value.

Non-clustered indexes are particularly handy when we want to return a single row from a table.

For example, to search for employee ID (empid = 3) in a table that has a non-clustered index on the empid column, SQL Server looks through the index to find an entry that lists the exact page and row in the table where the matching empid can be found, and then goes directly to that page and row. This greatly speeds up accessing the record in question.

Select EmpID, EmpName From DummyTable1 WHERE EMPID = 3

Now, let’s insert some more rows in our table and view the data page storage of our non-clustered index.

Insert Into DummyTable1 Values (10, Replicate ('j',2000))
GO

Insert Into DummyTable1 Values (2, Replicate ('b',2000))
GO

Insert Into DummyTable1 Values (5, Replicate ('e',2000))
GO

Insert Into DummyTable1 Values (8, Replicate ('h',2000))
GO

Insert Into DummyTable1 Values (9, Replicate ('i',2000))
GO

Insert Into DummyTable1 Values (7, Replicate ('g',2000))
GO

Now, let’s view the data in our table.

Execute:

Select EmpID From DummyTable1

Here are the results:

EmpID
4
6
1
3
10
2
5
8
9
7

As you may notice above, the data is still in the order we entered it, and not in any particular order. This is because adding the non-clustered index didn’t change how the data was stored and ordered on the data pages.

Now, let’s view the results of the DBCC IND command. In order to find out what happened when the new data was added to the table.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26408	0	10
26255	0	1
26409	0	1
26412	0	1
26413	0	1
26411	2	10
26410	2	2

Let us execute the page 26410 again and get the index page details.

DBCC TRACEON (3604)
GO

dbcc page(10, 1, 26410, 3)
GO

SQL Server populates the index column data in order.  The last column (?) is pointed to the row locator.

Here are the results:

Method I

FileID	PageID	EMPID	?
1	26410	1	0x8F66000001000200
1	26410	2	0x2C67000001000000
1	26410	3	0x2967000001000000
1	26410	4	0x8F66000001000000
1	26410	5	0x2C67000001000100
1	26410	6	0x8F66000001000100
1	26410	7	0x2D67000001000000
1	26410	8	0x2C67000001000200
1	26410	9	0x2967000001000200
1	26410	10	0x2967000001000100

As I explained earlier, there are two types of row locations. We have seen Method I.  Now, let’s try Method II with the help of a clustered and non-clustered index in a table. DummyTable1 already has a non-clustered index. Let’s now add a new column to the DummyTabl1 table and add a clustered index on that column. 

Alter Table DummyTable1 Add EmpIndex Int IDENTITY(1,1)
GO

This will link the clustered index key value, instead of the row locator, and be will the combination of fileno, pageno and no of rows in a page. 

This adds the Empindex column to DummyTable1. I have used an identity column so that we will not have null values on that column.

You can execute the DBCC ind and DBCC page to check if there any change after the new column is added to the table. If you don’t want to check this yourself, I can tell you that adding the new column did not affect the total number of pages currently allocated to the table by SQL Server.

Now, let’s add a unique clustered index on the empindex column and then view the differences in page 26410.

First, we execute the DBCC ind command.  This displays a new set of pages for dummytable1.

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id('DummyTable1')
DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID	IndexID	PageType
26415	1	10
26414	0	1
26416	1	2
26417	0	1
26418	0	1
26420	2	10
26419	2	2

Pages 26415 and 26420 have page allocation details.  Pages 26414, 26417 and 26418 have data page details.

Now, let’s view pages 26416 and 26419 and see the output.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26416, 3)
GO

Here are the results:

FileID	PageID	ChildPageID	EMPID
1	26416	26414	0
1	26416	26417	5
1	26416	26418	9

This displays the output of the clustered index page, which has got a link to data page (ChildPageID).  EMPID is an index column that contains the starting row of the page.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26419, 3)
GO

Here are the results:

Method II

FileID	PageID	EMPID	EMPIndex
1	26419	1	1
1	26419	2	2
1	26419	3	3
1	26419	4	4
1	26419	5	5
1	26419	6	6
1	26419	7	7
1	26419	8	8
1	26419	9	9
1	26419	10	10

It is interesting to see the differences now. There is a difference between Method I and Method II.  Method II is now linked to a clustered index key.

The main difference between Method I and Method II is the link to a row in a data page.