索引深入浅出(7/10)：非唯一列上的非聚集索引

在索引深入浅出：非聚集索引的B树结构在聚集表里，我们讨论了非聚集索引。我们提到，唯一非聚集索引可以让所讨论的问题变得简单很多。我们已经知道了非聚集索引的通用结构，现在我们来看下在非唯一列上的非聚集索引的存储结构。

在索引深入浅出：选择正确并合适的聚集索引键里，我们讨论了在非唯一列上如何管理聚集键。我们知道SQL Server会给所有发生重复的聚集键增加4 bytes的值。同样，非聚集索引在B树的所有层增加聚集键，让在下一层的记录标识唯一。至于聚集索引，uniquifier 只在重复时增加。对于非聚集索引，如果创建索引不唯一时，聚集键会在所有记录增加。如果非聚集索引是以唯一定义的，SQL Server只在叶子层增加聚集键，用做书签查找（bookmark lookup）操作。

我们来看一个例子。

SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008r2.Sales.SalesOrderDetail
GO
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)
GO
CREATE INDEX Ix_ProductId ON SalesOrderDetail(ProductId,Salesorderid)

我们创建了SalesOrderDetail表的副本，在SalesOrderDetailId 列定义了唯一聚集索引，在ProductId和SalesOrderId列定义了非聚集索引。注意，在创建非聚集索引的时候，我有意回避了使用Unique关键字，即使这个非聚集索引键是唯一的。

我们用DBCC IND看下非聚集索引的页面分配情况，并找出根页。

TRUNCATE TABLE dbo.sp_table_pages
INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetail,2)')
GO

SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页

可以看出，5128页是我们的根页。我们用DBCC PAGE看下5128页信息还有叶子页的信息。

DBCC TRACEON(3604)
DBCC PAGE(IndexDB,1,5128,3)--根页

DBCC TRACEON(3604)
DBCC PAGE(IndexDB,1,3760,3)--叶子页

我们来看上半部分的根页，聚集键（SalesOrderDetailid）被加到了根页。如果你和索引深入浅出：非聚集索引的B树结构在聚集表里的根页比较，会发现那里的根页里没有聚集键，只有在叶子页里才可以找到聚集键。在你把非聚集索引定义为唯一或非唯一（unique or non unique）时，叶子层的页结构不会发生改变。

我们看下堆表的情况。

SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008R2.Sales.SalesOrderDetail
GO
CREATE INDEX Ix_ProductId ON SalesOrderDetailHeap (ProductId,Salesorderid)
GO
SELECT index_id FROM sys.indexes WHERE name='Ix_ProductId' AND 
OBJECT_NAME(OBJECT_ID)='SalesOrderDetailHeap'
GO
DBCC ind('IndexDB','SalesOrderDetailHeap',2)
GO
TRUNCATE TABLE dbo.sp_table_pages
INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetailHeap,2)')
SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页
DBCC TRACEON(3604)
DBCC PAGE(IndexDB,1,1192,3)--根页

DBCC TRACEON(3604)
DBCC PAGE(IndexDB,1,1096,3)--叶子页

在根页我们看到多了Heap RID列，如果你回去看看索引深入浅出：非聚集索引的B树结构在堆表，你会发现Heap RID列只在叶子层里的页出现，不在根页出现。在你把非聚集索引定义为唯一或非唯一（unique or non unique）时，叶子层的页结构不会发生改变。

你可能已经注意到，在我们上述的例子里，即使非聚集索引键是唯一的，SQL Server还是只当它是非聚集索引，因为当我们创建非聚集索引时没加unique关键字。在索引所有层增加聚集键（或Heap RID）可能会增加更多的索引层IO操作，这个看聚集键的大小而定。因此在所有情况下，我们定义非聚集索引时，考虑选择唯一列（或多列）作为非聚集键非常重要。 

参考文章：

http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/24/sql-server-part-7-non-clustered-index-on-non-unique-column-/