Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions

By: Ben Snaidero   |   Updated: 2013-08-21   |   Comments (8)   |   Related: > Functions System

Problem

With every new version of SQL Server comes new and hopefully improved functionality. When SQL Server introduces a new function that sometimes overlaps or replaces the functionality of an already existing function, I am usually curious as to which performs better. This tip will compare the new PARSE function available in SQL Server 2012 with the good old CAST and CONVERT functions. It will also compare the new TRY_PARSE, TRY_CAST and TRY_CONVERT functions which are similar to the previous functions the only difference being they return NULL if the conversion fails rather than an error.  Check out this tip to learn more.

Solution

For those of you that aren't familiar with these new functions check out the following tip which gives a good overview of them:

• SQL Server 2012 - PARSE function

Since the new parse function deals only with converting data from string to either numeric or date types, we'll just focus on the following three test cases:

• VARCHAR -> INTEGER
• VARCHAR -> FLOAT
• VARCHAR -> DATETIME2

Sample table setup

After using the following table script you can copy some real data from an existing table in your environment into this table or generate some random test data using other tools.

-- Table creation logic
CREATE TABLE [dbo].[TestTable](
 [ColumnID] [int] NOT NULL,
 [IntegerString] [varchar](128) NULL,
 [FloatString] [varchar](128) NULL,
 [DateString] [varchar](50) NULL
) ON [PRIMARY]
GO

Once you've completed loading some random test data or using data from one of your existing environments, querying the table should result in data similar to the following:

Test script

Now that we have a table with some data loaded into it we can use the following script to test out each of the functions on each of the data types we've described above.

select PARSE(IntegerString AS int) from dbo.TestTable;
select CAST(IntegerString AS int) from dbo.TestTable;
select CONVERT(int,IntegerString) from dbo.TestTable;

select PARSE(FloatString AS float) from dbo.TestTable;
select CAST(FloatString AS float) from dbo.TestTable;
select CONVERT(float,FloatString) from dbo.TestTable;

select PARSE(DateString AS datetime2) from dbo.TestTable;
select CAST(DateString AS datetime2) from dbo.TestTable;
select CONVERT(datetime2,DateString) from dbo.TestTable;

select TRY_PARSE(IntegerString AS int) from dbo.TestTable;
select TRY_CAST(IntegerString AS int) from dbo.TestTable;
select TRY_CONVERT(int,IntegerString) from dbo.TestTable;

select TRY_PARSE(FloatString AS float) from dbo.TestTable;
select TRY_CAST(FloatString AS float) from dbo.TestTable;
select TRY_CONVERT(float,FloatString) from dbo.TestTable;

select TRY_PARSE(DateString AS datetime2) from dbo.TestTable;
select TRY_CAST(DateString AS datetime2) from dbo.TestTable;
select TRY_CONVERT(datetime2,DateString) from dbo.TestTable;

Gathering Results

After running the above script a bunch of times (I ran it 10 times so any anomalies are minimized), we can look at the performance of each function. In most cases I use Profiler to capture this data, but this time I thought I'd use the sys.dm_exec_query_stats DMV to gather this information. Given this is a conversion function I am mainly concerned with the amount of CPU consumed while it converts the data so we'll look at the total_worker_time column of the sys.dm_exec_query_stats DMV as well as the total_elapsed_time. We can use the following script to capture these two statistics.

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
    WHEN -1 THEN DATALENGTH(qt.TEXT)
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2)+1) AS Query,
  qs.Execution_count,
  ROUND(CAST(qs.total_worker_time AS float)/qs.execution_count,0) AS Avg_worker_time,
  ROUND(CAST(qs.total_elapsed_time AS float)/qs.execution_count,0) AS Avg_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.TEXT like '%TestTable%'

Now that we've gathered the statistics let's take a look at the results.

Results Analysis

Looking at the results above, we can see that in all cases either the CAST or CONVERT function outperforms the new PARSE function. The most interesting thing to note though is that there is no consistent pattern as to which function performs the best. In some cases CAST performs better than PARSE, which performs better than CONVERT. In other cases, CONVERT performs better than CAST, which performs better than PARSE. I guess the thing to take away from this is that as always it's best to perform your own testing, in your own environment, on your own data, to see which yields the best performance as there are many factors which could impact performance.

Larger Dataset Test

Let's also run this test on a couple of larger datasets to see if this factors into the results at all.  For this test, let's focus on the PARSE, CAST and CONVERT functions for the integer data type. We'll run the same queries as above on a table with 100,000 records and with 200,000 records. Here are the queries as a point of reference.

select PARSE(IntegerString AS int) from dbo.TestTable;
select CAST(IntegerString AS int) from dbo.TestTable;
select CONVERT(int,IntegerString) from dbo.TestTable;

Large dataset Results Analysis

Looking at these results we can see that the performance is fairly linear when dealing with larger datasets. This is a good thing for us in the sense that we don't have to worry about exponential performance degradation for our tables as they begin to grow.

Next Steps

• Further reading on other new functions available in SQL Server 2012:
  • Data type conversion functions
  • Logical functions
  • PercentRank and CumeDist functions
  • Analytical functions
  • Format and Concat functions
  • FirstValue and LastValue functions
• More information on using SQL Server DMVs for analyzing queries:
  • Identifying SQL Server bottlenecks
  • How to collect and store poor performing queries

About the author

Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips