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