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