索引键的唯一性（4/4）：非唯一聚集索引上的唯一和非唯一非聚集索引

在上一篇文章里，我谈了唯一聚集索引上的唯一和非唯一非聚集索引的区别。在这篇文章里，我想谈下非唯一聚集索引上的唯一和非唯一聚集索引的区别。我们都知道，SQL Server内部把非唯一聚集索引当作唯一聚集索引处理的。如果你定义了一个非唯一聚集索引，SQL Server会增加叫做uniquifier到你的索引记录，它导致你聚集索引的导航结构（B树的非叶子层）里，每条索引行都要用到4 bytes的开销。

下列代码再次创建我们的Customers表，这次在它上面定义非唯一聚集索引，最后定义2个非聚集索引，1个是唯一的，另1个是非唯一的。

 1 -- Create a table with 393 length + 7 bytes overhead = 400 bytes  2 -- Therefore 20 records can be stored on one page (8.096 / 400) = 20,24  3 CREATE TABLE Customers  4 (  5   CustomerID INT NOT NULL,  6   CustomerName CHAR(100) NOT NULL,  7   CustomerAddress CHAR(100) NOT NULL,  8   Comments CHAR(189) NOT NULL  9 ) 10 GO 11 12 -- Create a non unique clustered index on the previous created table 13 CREATE CLUSTERED INDEX idx_Customers ON Customers(CustomerID) 14 GO15 16 -- Insert 80.000 records 17 DECLARE @i INT = 1 18 WHILE (@i <= 20000) 19 BEGIN 20   DECLARE  @j INT = 1   21   INSERT INTO Customers VALUES 22   ( 23     @i, 24     'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR), 25     'CustomerAddress' + CAST(@i AS CHAR), 26     'Comments' + CAST(@i AS CHAR) 27   ) 28   29   SET @j += 1; 30   31   INSERT INTO Customers VALUES 32   ( 33     @i, 34     'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR), 35     'CustomerAddress' + CAST(@i AS CHAR), 36     'Comments' + CAST(@i AS CHAR) 37   ) 38   39   SET @j += 1; 40   41   INSERT INTO Customers VALUES 42   ( 43     @i, 44     'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR), 45     'CustomerAddress' + CAST(@i AS CHAR), 46     'Comments' + CAST(@i AS CHAR) 47   ) 48   49   SET @j += 1; 50   51   INSERT INTO Customers VALUES 52   ( 53     @i, 54     'CustomerName' + CAST(@i AS CHAR) + CAST(@j AS CHAR), 55     'CustomerAddress' + CAST(@i AS CHAR), 56     'Comments' + CAST(@i AS CHAR) 57   ) 58   59   SET @i += 1 60 END 61 GO62 63 -- Create a unique non clustered index on the clustered table 64 CREATE UNIQUE NONCLUSTERED INDEX idx_UniqueNCI_CustomerID 65 ON Customers(CustomerName) 66 GO 67 68 -- Create a non-unique non clustered index on the clustered table 69 CREATE NONCLUSTERED INDEX idx_NonUniqueNCI_CustomerID 70 ON Customers(CustomerName) 71 GO

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：索引键的唯一性（4/4）：非唯一聚集索引上的唯一和非唯一非聚集索引

关键词：