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