The output will display the data however it was entered in the table. This is how SQL stores the data in pages. Actually, 26255 & 26409 will 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 = dddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
Record Type = PRIMARY_RECORD
EmpId = 6
EmpName = ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
Record Type = PRIMARY_RECORD
EmpId = 1
EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
This displays the exact data storage in SQL without any index on table. Now, let’s go and create a Unique Clustered Index on EmpID column.
Create a Clustered Index
Now, let us create a unique clustered index on empid column to see how it affects the data that is stored in SQL Server.
CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex
ON DummyTable2 (EmpID)
GO
Execute:
Select EmpID From DummyTable2
Here are the results:
|
Empid |
|
1 |
|
3 |
|
4 |
|
6 |
Now, execute the DBCC ind (dbid, tabid, -1)
DBCC TRACEON (3604)
GO
Declare @DBID Int, @TableID Int
Select @DBID = db_id(), @TableID = object_id(‘DummyTable2’)
DBCC ind(@DBID, @TableID, -1)
GO
Here are the results:
|
PagePID |
IndexID |
PageType |
|
26411 |
1 |
10 |
|
26410 |
0 |
1 |
|
26412 |
1 |
2 |
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.
Now, let us view the page 26410 and 26412 and see the page details.
DBCC TRACEON (3604)
GO
DBCC page(10, 1, 26412, 3)
GO
Here are the results:
|
FileID |
PageID |
ChildPageID |
EMPID |
|
1 |
26412 |
26410 |
0 |
The output display many columns, but we are only interested in four of them as shown above.
This will display the output of the index page, which has got link to data page (ChildPageID). EMPID is an index column will contain the starting row of the page.
Now, let us view the page 26410 and see the page details.
DBCC TRACEON (3604)
GO
DBCC page (10, 1, 26410, 3)
GO
Here are the results:
Record Type = PRIMARY_RECORD
EmpId = 1
EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
Record Type = PRIMARY_RECORD
EmpId = 2
EmpName = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
Record Type = PRIMARY_RECORD
EmpId = 3
EmpName = cccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
Though I have added disorder records, SQL has displayed the data page in sequence because we have got a clustered index on empid. This is absolutely great! Adding a clustered index to the table has physically reordered the data pages, putting them in physical order based on the indexed column.