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