Actually looking directly at the index data it does not create a clustered index. Does exactly what I stated.
First I created a table like so.
CREATE TABLE [tbl_AgentList] (
[CCID] [int] NOT NULL ,
[AgentID] [int] NOT NULL ,
[AgentName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DteTme] [datetime] NOT NULL CONSTRAINT [DF_tbl_AgentList_DteTme] DEFAULT (getdate()),
CONSTRAINT [IX_tbl_AgentList] UNIQUE NONCLUSTERED
(
[CCID],
[AgentID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Then filled it will 632 rows.
Once done I ran
select * from sysindexes where [id] = object_id('tbl_AgentList') AND [name] = 'IX_tbl_AgentList'
and got the value from the column "first"
0x1F1A00000100
next I run
dbcc traceon(3604)
so I can actually get the data back from DBCC PAGE.
Then I transalated the value from first above to it's page value
Page File
1F1A0000 0100
Convert
1A1F0000 0001
or
6687 1
So page 6687 on filegroup 1
And thus I run
DBCC page(dbname,filegroup,page,option)
I prefer the layout of option 3
DBCC page('score',1,6687,3) --Score is my db name
and I see the following example data
PAGE: (1:6687)
--------------
BUFFER:
-------
BUF @0x00DA3180
---------------
bpage = 0x1B5EC000 bhash = 0x00000000 bpageno = (1:6687)
bdbid = 6 breferences = 3 bstat = 0xb
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1B5EC000
----------------
m_pageId = (1:6687) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId = 1364915934 m_indexId = 5 m_prevPage = (0:0)
m_nextPage = (1:6717) pminlen = 17 m_slotCnt = 426
m_freeCnt = 2 m_freeData = 7338 m_reservedCnt = 0
m_lsn = (6245:121:108) m_xactReserved = 0 m_xdesId = (0:0)
m_ghostRecCnt = 0 m_tornBits = 0
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level CCID AgentID ?
------ ----------- ------ ------ ----------- ----------- ------------------
1 6687 0 0 NULL NULL NULL
1 6687 1 0 2271 20126 0xD601000001000100
1 6687 2 0 2271 20127 0xD601000001000200
1 6687 3 0 2271 20128 0xD601000001000300
1 6687 4 0 2271 20130 0xD601000001000400 --I want this record
1 6687 5 0 2271 20131 0xD601000001000500
1 6687 6 0 2271 20132 0xD601000001000600
1 6687 7 0 2271 20133 0xD601000001000700
1 6687 8 0 2271 20145 0xD601000001000800
1 6687 9 0 2271 20147 0xD601000001000900
1 6687 10 0 2271 20148 0xD601000001000A00
1 6687 11 0 2271 20149 0xD601000001000B00
1 6687 12 0 2271 20150 0xD601000001000C00
1 6687 13 0 2271 20151 0xD601000001000D00
1 6687 14 0 2271 20152 0xD601000001000E00
1 6687 15 0 2271 21500 0xD601000001000F00
1 6687 16 0 2271 21502 0xD601000001001000
(Note: 0xD601000001000400
D6010000 0100 0400
Page File RowInThisIndex)
So the record I want has CCID 2271 and AgentID 20130 and its value D60100000100 leads me to Page 470 and filegroup 1
and thus I run
DBCC page('score',1,470,3)
PAGE: (1:470)
-------------
BUFFER:
-------
BUF @0x00D9D100
---------------
bpage = 0x1B2E8000 bhash = 0x00000000 bpageno = (1:470)
bdbid = 6 breferences = 23 bstat = 0x49
bspin = 0 bnext = 0x00000000
PAGE HEADER:
------------
Page @0x1B2E8000
----------------
m_pageId = (1:470) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId = 1364915934 m_indexId = 0 m_prevPage = (0:0)
m_nextPage = (1:159) pminlen = 20 m_slotCnt = 117
m_freeCnt = 3055 m_freeData = 5029 m_reservedCnt = 0
m_lsn = (5355:386:2) m_xactReserved = 0 m_xdesId = (0:1591211)
m_ghostRecCnt = 0 m_tornBits = 805439525
Allocation Status
-----------------
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x62 MIXED_EXT ALLOCATED 80_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
...Data removed always starts at Slot 0...
Slot 4 Offset 0xfe3
-------------------
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
1B2E8FE3: 00140030 000008df 00004ea2 00a414fa 0........N......
1B2E8FF3: 000092cc 01000004 45002a00 72617764 .........*.Edwar
1B2E9003: 202c7364 6e616d41 6164 ds, Amanda
CCID = 2271
AgentID = 20130
AgentName = Edwards, Amanda
DteTme = Nov 22 2002 9:57AM
...Data removed in this case it ended at slot 116 so a total of 117 records on the page...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I did not cross any clustered indexes with this non-clustered index.
Hope this helps a bit on understanding how an index tranverses the data.