SQLcommitted

Committed with SQL

SQL Server – Order of Column and Impact of equality operator on Index operation

Does order of Column in where clause matters?

Before you answer to this question I recommend all to read the blog post SQLAuthority.

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 order of column 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:

image

image

 

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 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

image

image

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

image

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

 

image

 

image

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:

  • Order of column 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.

If you liked this post, do like on Facebook at https://www.facebook.com/s4sql.

About these ads

December 24, 2012 - Posted by | Index | , , , , , ,

1 Comment »

  1. Nice article Sandip… expecting more interesting articles from you

    Comment by Mayur | December 24, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: