Column order and equality operator on Index

Does Column order  in where clause matters?

Whenever I have any doubt, I always play around with it to clarify my doubt. While doing experiment with index operation, I learnt something and here I’m demonstrating what I learnt?

Let’s do some experiment,

In below experiment I’m using the AdevntureWorks database, which can be downloadable from CodePlex.

Demo – 1 (Order doesn’t matter)

Create Customer1 table from Sales.Customer table.

SELECT * INTO Customer1 FROM Sales.Customer

Now Create an Index on Column CustomerID,TerritoryID

CREATE INDEX IXNC_Customer1_1 ON Customer1 (CustomerID,TerritoryID)

Run below two queries, the only difference in the queries are the column order in where clause.

–-Query – 1
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE CustomerID =1 AND TerritoryID =1
–-Query – 2
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE  TerritoryID =1 and CustomerID = 1

Compare Execution plan for both the queries:

We can see in the above Demo (Fig-1) both queries uses Index Seek and query cost is same.

Now compare index seek operation for both of the queries, Seek predicates prefix CustomerID as the first column in both the cases and both CustomerID and TerritoryID used by SQL server as seek predicates.

Before proceed further, from Demo- 1 we can conclude that the Column order doesn’t matter in where clause.

Demo – 2 (It matters if other than first column of the index used in where clause)

Now let’s see what will happen if we use only one column in where clause?

Run below two queries. Here I’ve used CustomerID in first query and TerritoryID in second query respectively in where clause.

-–Query – 3
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE  CustomerID = 1
-–Query – 4
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE  TerritoryID = 1

In Demo – 2, We can see that when we use Column CustomerID in Where clause it uses Seek Predicate for CustomerID Column but in Query – 4, there is no seek predicate because it is using Index scan.

In Fig – 4, The actual query plan shows the actual number of row are more in Query – 4, so we may assume that in second query, the number of resultant rows are more so it uses Index Scan and It doesn’t matter whether we use first column or second column of the index in where clause. It does matter if we use only one column of the index.

To prove that, now let’s see if we have only one row for TerritoryID is equal to 1, which operation will be used by SQL Server.

Below query will delete all records from Customer1 table except one row where TerritoryID = 1.

DELETE FROM Customer1 WHERE CustomerID >1 AND TerritoryID = 1

Confirm it by running below query that we have only one record in Customer1 table where TerritoryID = 1

SELECT * FROM Customer1 WHERE TerritoryID = 1

Now let’s run the same query that is Query – 4

-–Query – 5
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE  TerritoryID = 1

In above example we can see that even though there is only one row for TerritoryID = 1, It is not using TerritoryID column as seek predicate.

It is using Index scan because TerritoryID is not the first column of the index.

Demo – 3: The second column will participate in seek predicate Only If the first column of the index compares with equality operator irrespective of the order in where clause.

Note: As I’ve deleted some rows from the table. I’m dropping the table and recreating the Table and Index.

DROP TABLE Customer1
SELECT * INTO Customer1 FROM Sales.Customer
CREATE INDEX IXNC_Customer1_1 ON Customer1 (CustomerID,TerritoryID)
GO

Let’s run below two queries, where in first query, CustomerID is compared with equality operator and second query, CustomerID is compared with non-equality operator.

–-Query – 6
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE  TerritoryID >1 AND CustomerID = 1
–-Query – 7
SELECT CustomerID, TerritoryID
FROM Customer1
WHERE TerritoryID > 1 AND CustomerID > 1

In the above example, we can see that even though both the queries uses Index seek operation but in Query – 5, Both CustomerID and TerritoryID columns are participated in Seek Predicate and in Query – 6 only CustomerID is participated in Seek predicate and TerritoryID is used as filter predicate.

Conclusion:

• Column order in where clause doesn’t matter.
• It matters if we use Columns except first column of the Index.
• If first column of the index compared with equality operator then only second column will participate in Seek predicate