索引键的唯一性（1/4）：堆表上的唯一与非唯一非聚集索引的区别

 1 USE ALLOCATIONDB
 2 -- Create a table with 393 length + 7 bytes overhead = 400 bytes 
 3 -- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24 
 4 CREATE TABLE CustomersHeap 
 5 ( 
 6     CustomerID INT NOT NULL, 
 7     CustomerName CHAR(100) NOT NULL, 
 8     CustomerAddress CHAR(100) NOT NULL, 
 9     Comments CHAR(189) NOT NULL 
10 ) 
11 GO
12 
13 -- Insert 80.000 records 
14 DECLARE @i INT = 1 
15 WHILE (@i <= 80000) 
16 BEGIN 
17     INSERT INTO CustomersHeap VALUES 
18     ( 
19         @i, 
20         'CustomerName' + CAST(@i AS CHAR), 
21         'CustomerAddress' + CAST(@i AS CHAR), 
22         'Comments' + CAST(@i AS CHAR) 
23     )     
24     SET @i += 1 
25 END 
26 GO
27 
28 -- Retrieve physical information about the heap table 
29 SELECT * FROM sys.dm_db_index_physical_stats 
30 ( 
31     DB_ID('ALLOCATIONDB'), 
32     OBJECT_ID('CustomersHeap'), 
33     NULL, 
34     NULL, 
35     'DETAILED'
36 ) 
37 GO

1 -- Create a unique non clustered index 
2 CREATE UNIQUE NONCLUSTERED INDEX IDX_UniqueNCI_CustomerID 
3 ON CustomersHeap(CustomerID) 
4 GO  
5 
6 -- Create a non-unique non clustered index 
7 CREATE NONCLUSTERED INDEX IDX_NonUniqueNCI_CustomerID 
8 ON CustomersHeap(CustomerID) 
9 GO 

 1 -- Retrieve physical information about the unique non-clustered index 
 2 SELECT * FROM sys.dm_db_index_physical_stats 
 3 ( 
 4     DB_ID('ALLOCATIONDB'), 
 5     OBJECT_ID('CustomersHeap'), 
 6     2, 
 7     NULL, 
 8     'DETAILED'
 9 ) 
10 GO
11 
12 -- Retrieve physical information about the non-unique non-clustered index 
13 SELECT * FROM sys.dm_db_index_physical_stats 
14 ( 
15     DB_ID('ALLOCATIONDB'), 
16     OBJECT_ID('CustomersHeap'), 
17     3, 
18     NULL, 
19     'DETAILED'
20 ) 
21 GO

 1 -- Create a helper table 
 2 CREATE TABLE sp_table_pages 
 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 TINYINT, 
18    PrevPagePID INT, 
19    PRIMARY KEY (PageFID, PagePID) 
20 ) 
21 GO
22 
23 -- Write everything in a table for further analysis 
24 INSERT INTO sp_table_pages 
25 EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 2)') 
26 GO
27 
28 -- Write everything in a table for further analysis 
29 INSERT INTO sp_table_pages 
30 EXEC('DBCC IND(ALLOCATIONDB, CustomersHeap, 3)') 
31 GO

1 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC,IndexID

1 DBCC TRACEON(3604) 
2 GO
3 DBCC PAGE(ALLOCATIONDB, 1, 14624, 3) 
4 GO

1 DBCC TRACEON(3604) 
2 GO
3 DBCC PAGE(ALLOCATIONDB, 1, 14624, 1) 
4 GO

1 DBCC TRACEON(3604) 
2 GO
3 DBCC PAGE(ALLOCATIONDB, 1, 14537, 3) 
4 GO

1 DBCC TRACEON(3604) 
2 GO
3 DBCC PAGE(ALLOCATIONDB, 1, 14537, 1) 
4 GO

1 DBCC TRACEON(3604) 
2 GO
3 DBCC PAGE(ALLOCATIONDB, 1, 14608, 3) 
4 GO

1 CREATE NONCLUSTERED INDEX ...