1 -- Create a new table 2 CREATE TABLE Customers 3 ( 4 CustomerName VARCHAR(255) NOT NULL PRIMARY KEY, 5 Filler CHAR(138) NOT NULL 6 ) 7 GO 8 9 -- Insert 80.000 records 10 DECLARE @i INT = 1 11 WHILE (@i <= 80000) 12 BEGIN 13 INSERT INTO Customers VALUES 14 ( 15 'CustomerName' + CAST(@i AS VARCHAR), 16 'Filler' + CAST(@i AS VARCHAR) 17 ) 18 19 SET @i += 1 20 END 21 GO |
从代码里我们可以看到，我在VARCHAR(255)列上建立了主键约束，SQL Server会强制这列为唯一聚集索引。接下来我们通过DMV sys.dm_db_index_physical_stats来获取聚集索引的相关物理信息：
1 -- Retrieve physical information about the clustered index
2 SELECT * FROM sys.dm_db_index_physical_stats
3 (
4 DB_ID('ALLOCATIONDB'),
5 OBJECT_ID('Customers'),
6 NULL,
7 NULL,
8 'DETAILED'
9 )
10 GO
从输出结果可以看出，在索引页里，min_record_size_in_bytes列的值是7，max_record_size_in_bytes列的值是28。我们据此可以得出结论：在索引记录内部，聚集键是以变长列保存的。我们建立一个帮助表来存储DBCC IND的输出信息来做进一步分析。
1 -- Create a helper table 2 CREATE TABLE HelperTable 3 ( 4 PageFID TINYINT, 5 PagePID INT, 6 IAMFID TINYINT, 7 IAMPID INT, 8 ObjectID INT, 9 IndexID TINYINT, 10 PartitionNumber TINYINT, 11 PartitionID BIGINT, 12 iam_chain_type VARCHAR(30), 13 PageType TINYINT, 14 IndexLevel TINYINT, 15 NextPageFID TINYINT, 16 NextPagePID INT, 17 PrevPageFID INT, 18 PrevPagePID INT, 19 PRIMARY KEY (PageFID, PagePID) 20 ) 21 GO 22 23 -- Write everything in a table for further analysis 24 INSERT INTO HelperTable EXEC('DBCC IND(ALLOCATIONDB, Customers, 1)') 25 GO 26 27 -- Retrieve the root index page (1 page) 28 SELECT * FROM HelperTable 29 WHERE IndexLevel = 2 30 GO |
