174
Chapitre 6. Utilisation des index
PartitionNumber bigint,
PartitionID bigint,
iam_chain_type varchar(20),
PageType int,
IndexLevel int,
NextPageFID bigint,
NextPagePID bigint,
PrevPageFID bigint,
PrevPagePID bigint
)
GO
INSERT INTO #ind
EXEC ('DBCC IND(''testdb'', ''dbo.testIndex'', 2)')
SELECT * FROM #ind WHERE IndexLevel = 5
-- comment résoudre cette requête avec un index
SET STATISTICS IO ON
SELECT * FROM dbo.testIndex WHERE code = '49'
/*
Table 'testIndex'. Scan count 0, logical reads 7
*/
DBCC TRACEON (3604);
GO
DBCC PAGE (testdb, 1, 8238, 3)
/*
FileId PageId
Row
Level ChildFileId ChildPageId codeLong (key)
------ ----------- ------ ------ ----------- ----------- ---------1
8238
0
5
1
4179
NULL
1
8238
1
5
1
8239
253
1
8238
2
5
1
1122
45
*/
-- row, key 45, ChildPageId : 1122
DBCC PAGE (testdb, 1, 1122, 3)
/*
FileId PageId
Row
Level ChildFileId ChildPageId codeLong (key)
------ ----------- ------ ------ ----------- ----------- ---------1
1122
0
4
1
4180
45
1
1122
1
4
1
9582
487
1
1122
2
4
1
9121
55
[...]
*/
-- row, key 487, ChildPageId : 9582
DBCC PAGE (testdb, 1, 9582, 3)
/*
FileId PageId
Row
Level ChildFileId ChildPageId codeLong (key)
------ ----------- ------ ------ ----------- ----------- ---------1
9582
0
3
1
9122
487
1
9582
1
3
1
9207
495
1
9582
2
3
1
9368
504
[...]