Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Do Nonclustered Indexes Contain the Primary Key?

  • 14 April 2013
  • Author: Kathi Kellenberger
  • Number of views: 6003
  • 0 Comments

A very interesting question came up during my recent Training on the T’s presentation on indexes concerning the leaf level of nonclustered indexes. If the cluster key is made up of the primary key does the leaf level contain the primary key? The answer is no, it contains the cluster key. To test this, do the following:

Connect to a copy of AdventureWorks2008R2 or AdventureWorks2012 and run the following code to create a table with a primary key of SalesOrderID, but a cluster key of OrderDate. It also has a nonclustered index on ShipDate.
USE AdventureWorks2008R2
GO
SELECT * INTO dbo.SalesOrderHeader
FROM Sales.SalesOrderHeader;
GO
CREATE CLUSTERED INDEX CI_SalesOrderHeader ON dbo.SalesOrderHeader(OrderDate)
GO
ALTER TABLE dbo.SalesOrderHeader ADD CONSTRAINT
 PK_SalesOrderHeader PRIMARY KEY NONCLUSTERED
 ( SalesOrderID )
GO
CREATE NONCLUSTERED INDEX NC_SalesOrderHeader_ShipDate ON dbo.SalesOrderHeader(ShipDate)

In order to show that the leaf level of the nonclustered index contains the cluster key (OrderDate), run this query and take a look at the Execution Plan.

SELECT OrderDate, ShipDate
FROM dbo.SalesOrderHeader
WHERE ShipDate = '1/1/2008';


The query was satisfied by performing an Index Seek. Since the ShipDate is the only column in the nonclustered index and the query did not require touching the actual table, the cluster key, OrderDate, is actually part of the nonclustered index.
Now, to find out if the primary key (SalesOrderID) is stored in the nonclustered index, run this query:


SELECT SalesOrderID, ShipDate
FROM dbo.SalesOrderHeader
WHERE ShipDate = '1/1/2008';


 


In this case, SQL Server had to retrieve the SalesOrderID values from the table since the column was not part of the nonclustered index. In the majority of cases, the primary key and cluster key are one and the same. When adding a primary key to a table, if a clustered index does not already exists and the nonclustered option is not specified, SQL Server will automatically add a clustered index to the table with the same key.

 

Print
Categories: SQL Server
Tags:
Rate this article:
No rating

Please login or register to post comments.