CAST function doesn’t change execution plan for Datetime type column

I have been reviewing code from last couple of years. I found some myths are followed very strictly by developers. Yesterday while reviewing, I found developers are hesitating to use TRY_CAST or CAST function on column in where clause to convert date to datetime or datetime to date. When I asked the reason they said use of function on column will not use index created on that column.

It is true that use of function on columns in search predicate or join condition will not pick the correct index. This is true for almost all cases except date or datetime datatype column. Let’s run the below code. For below example I am using AdventureWorks DB ( it can be downloaded from codeplex.

--Create a copy of SalesOrderDetail table 

Select * into SalesOrderDetailTemp from [Sales].[SalesOrderDetail]

--Create an index on Modifieddate column which is of datetime data type and one more index on ProductID which is of int data type

CREATE Index IX_ModifiedDate ON SalesOrderDetailTemp(ModifiedDate) Include(ProductID, OrderQty, UnitPrice)

CREATE Index IX_ProductID ON SalesOrderDetailTemp(ProductID) Include(ModifiedDate, OrderQty, UnitPrice)

Now enable actual execution plan and run below two queries.

Query 1:

select  ProductID,OrderQty, UnitPrice from SalesOrderDetailTemp where CAST(ModifiedDate AS DATE) ='2005-07-01'

 

CAST 1

Here, though we use CAST function on column ModifiedDate, it is using the right index. Now let’s run the below query.

Query 2:

select  ProductID,OrderQty, UnitPrice from SalesOrderDetailTemp where CAST(ProductID AS varchar) ='776'

cast2

You can see in the above plan when we use CAST on column of type int , it is showing a warning message and using wrong index.

It is safe to use cast function on column of type Date or Datetime.

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

2 Comments on “CAST function doesn’t change execution plan for Datetime type column”

  1. Hi Sandip,

    I replicated the same thing into my SQL Server environment and below are my findings.
    1. Your first query uses the right index(IX_ModifiedDate) because you took all the fields in INCLUDE clause of the index which are required in this query select list. If i remove the INCLUDE clause the same query wont use this index.
    2. For second query i use this command-
    select ModifiedDate,OrderQty,UnitPrice from SalesOrderDetailTemp where convert(varchar,ProductID) = ‘712’
    For this also, the sql uses “IX_ProductID” index while i have both the indexes with INCLUDE part.

    1. Hi Mithun,
      Thank you for your comments.
      Point 1: This is the normal behavior, if sql server doesn’t find any index which covers the query then it won’t use that index. here I wanted to demonstrate if you have a covering index and if the column is used in a function still it will use that index even though the column is used in a function.

      Point 2: I missed to mention the second query. I have included query 2. In your second case if you see it must be using the IX_ProductID but it must be a index scan. Though the selectivity is 1. It is again a normal behavior.

Leave a Reply

Your email address will not be published. Required fields are marked *