Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Conversion operations in datetime data types often cause poor performance but they are always the ones that are overlooked. We will examine the Cast and Convert Performance in SQL Server in this article.
Yes, they both do the same job, but as a result of our example, there can be big differences between them according to the query design and the scenario where it is used.
Convert Syntax
1 | CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) |
Cast Syntax
1 | CAST ( expression AS data_type [ ( length ) ] ) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE DatePerformance ( Id INT IDENTITY PRIMARY KEY , Descriptions CHAR(500) DEFAULT 'Yusuf Kahveci' , DateColumn DATETIME, ); INSERT INTO DatePerformance ( DateColumn ) SELECT DATEADD(MI, RAND() * 10000, GETDATE()) GO 100000 |
1 2 3 4 | CREATE INDEX SQLNCIX_DataPerformance_DateColumn ON DatePerformance ( DateColumn); |
Query I:
1 2 3 4 5 6 7 8 9 10 | -- SESSION I SET STATISTICS IO ON; DECLARE @SqlTr_Date DATETIME = '2016-07-15 16:38:49.490'; SELECT DateColumn FROM DatePerformance WHERE CONVERT(VARCHAR, DateColumn, 112) = CONVERT(VARCHAR, @SqlTr_Date, 112); SET STATISTICS IO OFF; |
Query II :
1 2 3 4 5 6 7 8 | SET STATISTICS IO ON; DECLARE @SqlTr_Date DATETIME = '2016-07-15 16:38:49.490'; SELECT DateColumn FROM DatePerformance WHERE CAST(DateColumn AS DATE) = CAST(@SqlTr_Date AS DATE); SET STATISTICS IO OFF; |
When we examine the above Queries, we see the expression “SET STATISTICS IO ON”. With this expression we get the IO values of our Query. Thus, we will be able to evaluate the Performance by comparing the IO values of the two Query.
Also press the “Actual Execution Plan” button before pressing the Execute button. Thus, it will prepare the Execution Plan and we can compare Execution Plans.
You can see the statistical value and Execution Plan that created when we Execute Query I.
Q I – Statistics:
(14392 row(s) affected)
Table ‘DatePerformance’. Scan count 1, logical reads 825, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Q I – Execution Plan :
Q II – Statistics:
(14392 row(s) affected)
Table ‘DatePerformance’. Scan count 1, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
Q II – Execution Plan :
As we have seen, we have written two separate queries with the CONVERT and CAST functions and found that there is a serious IO difference and Execution Plan difference in performance evaluation.
We have seen in our article that we need to be careful in our processes such as date formatting. We have also realized that it is very important that we use functions by analyzing Query design.
Good luck with.
