The order of the columns in a composite index does matter on how a query against a table will use it or not. A query will use a composite index only if the where clause of the query has at least the leading/left most columns of the index in it.
Let us understand how the order of a column in a composite index matters with an example. Let us create a Customer table as shown in the below image with sample one million records by executing the following script.
CREATE DATABASE SqlHints101PerfTips3 GO USE SqlHints101PerfTips3 GO --Create Demo Table Customers CREATE TABLE dbo.Customers ( CustomerId INT IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50), LastName VARCHAR(50), PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50), CreationDate DATETIME ) GO --Populate 1 million dummy customer records INSERT INTO dbo.Customers (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate) SELECT TOP 1000000 REPLACE(NEWID(),'-',''), REPLACE(NEWID(),'-',''), CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,0) AS BIGINT) AS VARCHAR(10)), REPLACE(NEWID(),'-','') + '@gmail.com', DATEADD(HOUR,CAST(RAND(CHECKSUM(NEWID())) * 19999 as INT) + 1 ,'2006-01-01') FROM sys.all_columns c1 CROSS JOIN sys.all_columns c2 GO --Update one customer record with some know values UPDATE dbo.Customers SET FirstName = 'Basavaraj', LastName = 'Biradar', PhoneNumber = '4545454545', EmailAddress = 'basav@gmail.com' WHERE CustomerId = 100000 GO --Create a PK and a Clustered Index on CustomerId column ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers_CustomerId PRIMARY KEY CLUSTERED (CustomerId) GO
Create a Composite Non-Clustered Index on FirstName and LastName columns
Create a Composite Non-Clustered Index on FirstName and LastName column of the Customers Table by executing following statement:
--Create a non-clustered index on FirstName column CREATE NONCLUSTERED INDEX IX_Customers_FirstName_LastName ON dbo.Customers (FirstName, LastName) GO
First let us now enable the execution plan in the Sql Server Management Studio by pressing the key stroke CTRL + M
Let us now verify how the Order of the columns in the above composite index impacts it’s usage for the queries against this table
Scanerio 1: Query having FirstName and LastName column in the WHERE clause in the same order
SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE FirstName = 'Basavaraj' AND LastName = 'Biradar'
Below is the execution plan for the above query execution
From the above execution plan we can see that the composite index IX_Customers_FirstName_LastName is used and it is performing the INDEX SEEK of it.
Scanerio 2: Query having LastName and FirstName column in the WHERE clause in the same order
SELECT CustomerId, FirstName, LastName FROM dbo.Customers WHERE LastName = 'Biradar' AND FirstName = 'Basavaraj'
Below is the execution plan for the above query execution
From the above execution plan we can see that the composite index IX_Customers_FirstName_LastName is used and it is performing the INDEX SEEK of it.
Scanerio 3: Query having only FirstName column in the WHERE clause
SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE FirstName = 'Basavaraj'
Below is the execution plan for the above query execution
From the above execution plan we can see that the composite index IX_Customers_FirstName_LastName is used and it is performing the INDEX SEEK of it.
Scanerio 4: Query having only LastName column in the WHERE clause
SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE LastName = 'Biradar'
Below is the execution plan for the above query execution
From the above execution plan we can see that the composite index IX_Customers_FirstName_LastName is used but it is performing the INDEX SCAN of it. Also we can see from the execution plan that, Sql Server has given missing index hint and it is suggesting to add Index on the LastName column.
Verify the Cost of the Query
Execute the following four statement in one batch and verify the cost of the query
SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE FirstName = 'Basavaraj' AND LastName = 'Biradar' GO SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE LastName = 'Biradar' AND FirstName = 'Basavaraj' GO SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE FirstName = 'Basavaraj' GO SELECT CustomerId, FirstName, LastName FROM dbo.Customers WITH(NOLOCK) WHERE LastName = 'Biradar' GO
From the above execution plan we can see that the queries which have the leading column FirstName of the Index in the WHERE clause have 0% cost compared to the last query which has 100% cost which has only LastName non-leading column of the index in the WHERE clause.
Conclusion:
In Sql Server the order of the columns in a composite index does matter on how a query against a table will use it or not. A query will use a composite index only if the where clause of the query has at least the leading/left most columns of the index in it. In the example listed in this article we can see that all the queries having the leading Index column FirstName in the WHERE clause are using the Index. And the query having only the non-leading column LastName of the Index in the WHERE clause is not doing the Index Seek
Hello Sir,
Great Explanation.
We need for article sir.
Thanks a lot. 🙂
Thanks Hitesh. Appreciate your comments…
Its really helpful for me. Thanks a lot. We need your update in future also. Thank you once again.
Thanks man! I really appreciate your clear and detailed explanation .
Great explanation. This article helped me a lot. Thank you once again for this 🙂 .