heap/clustered index / nonclustered index
---------------------------------------------------------------------------------------------------------------------
-- Heap
---------------------------------------------------------------------------------------------------------------------
drop table [TestTable]
CREATE TABLE [dbo].[TestTable]
(
[id] [int] NOT NULL,
[FirstName] [NVARCHAR](35),
[LastName] [NVARCHAR](35)
-- CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([id] ASC)
);
insert into [TestTable] values(1,'AAAAAA','LastNameLastName'),(3,'CCCCCC','LastNameLastName')
insert into [TestTable] values(2,'BBBBBB','LastNameLastName')
SELECT sys.fn_PhysLocFormatter(t.%%physloc%%), * FROM [dbo].[TestTable] as t;
DBCC IND(testDB, 'TestTable', -1);
DBCC TRACESTATUS
DBCC TRACEON (3604)
DBCC PAGE (TestDB, 1, 560, 3);
PAGE: (1:560)
BUFFER:
BUF @0x0000019ADEFAC600
bpage = 0x000001932D2CE000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000000000000000
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000 bpageno = (1:560)
bpart = 4 bstat = 0xb breferences = 0
berrcode = 0 bUse1 = 3502 bstat2 = 0x0
blog = 0xcccccccc bsampleCount = 0 bIoCount = 0
resPoolId = 0 bcputicks = 0 bReadMicroSec = 1909
bDirtyPendingCount = 0 bDirtyContext = 0x00000193069DD790 bDbPageBroker = 0x0000000000000000
bdbid = 10 bpru = 0x000001930E558040
PAGE HEADER:
Page @0x000001932D2CE000
m_pageId = (1:560) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 243 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594053853184
Metadata: PartitionId = 72057594047102976 Metadata: IndexId = 0
Metadata: ObjectId = 34099162 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 7907
m_freeData = 279 m_reservedCnt = 0 m_lsn = (40:56:25)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x41 ALLOCATED 50_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x00000086591F6060
0000000000000000: 30000800 01000000 03000002 001d003d 00410041 0..............=.A.A
0000000000000014: 00410041 00410041 004c0061 00730074 004e0061 .A.A.A.A.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1
Slot 0 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = AAAAAA
Slot 0 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 1 Offset 0x9d Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x00000086591F609D
0000000000000000: 30000800 03000000 03000002 001d003d 00430043 0..............=.C.C
0000000000000014: 00430043 00430043 004c0061 00730074 004e0061 .C.C.C.C.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 3
Slot 1 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = CCCCCC
Slot 1 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 2 Offset 0xda Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x00000086591F60DA
0000000000000000: 30000800 02000000 03000002 001d003d 00420042 0..............=.B.B
0000000000000014: 00420042 00420042 004c0061 00730074 004e0061 .B.B.B.B.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 2
Slot 2 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = BBBBBB
Slot 2 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。
Completion time: 2024-01-04T14:57:11.1387787+08:00
---------------------------------------------------------------------------------------------------------------------
-- Clustered Index
---------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[TestTable]
(
[id] [int] NOT NULL,
[FirstName] [NVARCHAR](35),
[LastName] [NVARCHAR](35)
,CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([id] ASC)
);
insert into [TestTable] values(1,'AAAAAA','LastNameLastName'),(3,'CCCCCC','LastNameLastName')
insert into [TestTable] values(2,'BBBBBB','LastNameLastName')
SELECT sys.fn_PhysLocFormatter(t.%%physloc%%), * FROM [dbo].[TestTable] as t;
DBCC IND(testDB, 'TestTable', -1);
DBCC PAGE (TestDB, 1, 560, 3);
PAGE: (1:560)
BUFFER:
BUF @0x0000019ADEFAC600
bpage = 0x000001932D2CE000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000000000000000
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000 bpageno = (1:560)
bpart = 4 bstat = 0xb breferences = 0
berrcode = 0 bUse1 = 3644 bstat2 = 0x0
blog = 0xcccccccc bsampleCount = 0 bIoCount = 0
resPoolId = 0 bcputicks = 0 bReadMicroSec = 1909
bDirtyPendingCount = 0 bDirtyContext = 0x00000193069DD790 bDbPageBroker = 0x0000000000000000
bdbid = 10 bpru = 0x000001930E558040
PAGE HEADER:
Page @0x000001932D2CE000
m_pageId = (1:560) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 244 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594053918720
Metadata: PartitionId = 72057594047168512 Metadata: IndexId = 1
Metadata: ObjectId = 50099219 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 7907
m_freeData = 279 m_reservedCnt = 0 m_lsn = (40:120:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865CE76060
0000000000000000: 30000800 01000000 03000002 001d003d 00410041 0..............=.A.A
0000000000000014: 00410041 00410041 004c0061 00730074 004e0061 .A.A.A.A.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1
Slot 0 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = AAAAAA
Slot 0 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (8194443284a0)
Slot 1 Offset 0xda Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865CE760DA
0000000000000000: 30000800 02000000 03000002 001d003d 00420042 0..............=.B.B
0000000000000014: 00420042 00420042 004c0061 00730074 004e0061 .B.B.B.B.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 2
Slot 1 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = BBBBBB
Slot 1 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (61a06abd401c)
Slot 2 Offset 0x9d Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865CE7609D
0000000000000000: 30000800 03000000 03000002 001d003d 00430043 0..............=.C.C
0000000000000014: 00430043 00430043 004c0061 00730074 004e0061 .C.C.C.C.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 3
Slot 2 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = CCCCCC
Slot 2 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 2 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (98ec012aa510)
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。
Completion time: 2024-01-04T14:59:32.7142723+08:00
---------------------------------------------------------------------------------------------------------------------
-- Non Clustered Index
---------------------------------------------------------------------------------------------------------------------
drop table [TestTable]
GO
CREATE TABLE [dbo].[TestTable]
(
[id] [int] NOT NULL,
[FirstName] [NVARCHAR](35),
[LastName] [NVARCHAR](35)
--,CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO
create nonclustered index idx_Nclu on TestTable (FirstName);
GO
insert into [TestTable] values(1,'AAAAAA','LastNameLastName'),(3,'CCCCCC','LastNameLastName')
insert into [TestTable] values(2,'BBBBBB','LastNameLastName')
SELECT sys.fn_PhysLocFormatter(t.%%physloc%%), * FROM [dbo].[TestTable] as t;
DBCC IND(testDB, 'TestTable', -1);
PAGE: (1:560)
BUFFER:
BUF @0x0000019ADEFAC600
bpage = 0x000001932D2CE000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000000000000000
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000 bpageno = (1:560)
bpart = 4 bstat = 0xb breferences = 0
berrcode = 0 bUse1 = 3911 bstat2 = 0x0
blog = 0xcccccccc bsampleCount = 0 bIoCount = 0
resPoolId = 0 bcputicks = 0 bReadMicroSec = 1909
bDirtyPendingCount = 0 bDirtyContext = 0x00000193069DD790 bDbPageBroker = 0x0000000000000000
bdbid = 10 bpru = 0x000001930E558040
PAGE HEADER:
Page @0x000001932D2CE000
m_pageId = (1:560) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 246 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594054049792
Metadata: PartitionId = 72057594047299584 Metadata: IndexId = 0
Metadata: ObjectId = 98099390 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 7907
m_freeData = 279 m_reservedCnt = 0 m_lsn = (40:216:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x41 ALLOCATED 50_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865B476060
0000000000000000: 30000800 01000000 03000002 001d003d 00410041 0..............=.A.A
0000000000000014: 00410041 00410041 004c0061 00730074 004e0061 .A.A.A.A.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1
Slot 0 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = AAAAAA
Slot 0 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 1 Offset 0x9d Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865B47609D
0000000000000000: 30000800 03000000 03000002 001d003d 00430043 0..............=.C.C
0000000000000014: 00430043 00430043 004c0061 00730074 004e0061 .C.C.C.C.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 3
Slot 1 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = CCCCCC
Slot 1 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 2 Offset 0xda Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865B4760DA
0000000000000000: 30000800 02000000 03000002 001d003d 00420042 0..............=.B.B
0000000000000014: 00420042 00420042 004c0061 00730074 004e0061 .B.B.B.B.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 2
Slot 2 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = BBBBBB
Slot 2 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。
Completion time: 2024-01-04T15:04:00.1777153+08:00
DBCC PAGE (TestDB, 1, 576, 3); ---type 2, index page
--------------------------------------------------------------------------------------------------------------------
-- Clustered Index + Non Clustered Index
---------------------------------------------------------------------------------------------------------------------
drop table [TestTable]
GO
CREATE TABLE [dbo].[TestTable]
(
[id] [int] NOT NULL,
[FirstName] [NVARCHAR](35),
[LastName] [NVARCHAR](35)
,CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([id] ASC)
);
GO
create nonclustered index idx_Nclu on TestTable (FirstName);
GO
insert into [TestTable] values(1,'AAAAAA','LastNameLastName'),(3,'CCCCCC','LastNameLastName')
insert into [TestTable] values(2,'BBBBBB','LastNameLastName')
SELECT sys.fn_PhysLocFormatter(t.%%physloc%%), * FROM [dbo].[TestTable] as t;
DBCC IND(testDB, 'TestTable', -1);
insert into [TestTable] values('A'),('B')
SELECT sys.fn_PhysLocFormatter(t.%%physloc%%), * FROM [dbo].[TestTable] as t;
DBCC IND(testDB, 'TestTable', -1);
DBCC PAGE (TestDB, 1, 560, 3);
PAGE: (1:560)
BUFFER:
BUF @0x0000019ADEFAC600
bpage = 0x000001932D2CE000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000000000000000
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000 bpageno = (1:560)
bpart = 4 bstat = 0xb breferences = 0
berrcode = 0 bUse1 = 4175 bstat2 = 0x0
blog = 0xcccccccc bsampleCount = 0 bIoCount = 0
resPoolId = 0 bcputicks = 0 bReadMicroSec = 1909
bDirtyPendingCount = 0 bDirtyContext = 0x00000193069DD790 bDbPageBroker = 0x0000000000000000
bdbid = 10 bpru = 0x000001930E558040
PAGE HEADER:
Page @0x000001932D2CE000
m_pageId = (1:560) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 248 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594054180864
Metadata: PartitionId = 72057594047430656 Metadata: IndexId = 1
Metadata: ObjectId = 114099447 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 3 m_freeCnt = 7907
m_freeData = 279 m_reservedCnt = 0 m_lsn = (40:288:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865B476060
0000000000000000: 30000800 01000000 03000002 001d003d 00410041 0..............=.A.A
0000000000000014: 00410041 00410041 004c0061 00730074 004e0061 .A.A.A.A.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1
Slot 0 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = AAAAAA
Slot 0 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (8194443284a0)
Slot 1 Offset 0xda Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865B4760DA
0000000000000000: 30000800 02000000 03000002 001d003d 00420042 0..............=.B.B
0000000000000014: 00420042 00420042 004c0061 00730074 004e0061 .B.B.B.B.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 2
Slot 1 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = BBBBBB
Slot 1 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 1 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (61a06abd401c)
Slot 2 Offset 0x9d Length 61
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 61
Memory Dump @0x000000865B47609D
0000000000000000: 30000800 03000000 03000002 001d003d 00430043 0..............=.C.C
0000000000000014: 00430043 00430043 004c0061 00730074 004e0061 .C.C.C.C.L.a.s.t.N.a
0000000000000028: 006d0065 004c0061 00730074 004e0061 006d0065 .m.e.L.a.s.t.N.a.m.e
000000000000003C: 00 .
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 3
Slot 2 Column 2 Offset 0x11 Length 12 Length (physical) 12
FirstName = CCCCCC
Slot 2 Column 3 Offset 0x1d Length 32 Length (physical) 32
LastName = LastNameLastName
Slot 2 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (98ec012aa510)
DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息，請連絡您的系統管理員。
Completion time: 2024-01-04T15:08:23.8715791+08:00
DBCC PAGE (TestDB, 1, 576, 3);
============================
有clustered index時
沒有clusteredindex時
