SQL Server Data Type Conversion Methods and performance comparison

When you define SQL Server database tables, local variables, expressions or parameters, you should specify what kind of data will be stored in those objects, such as text data, numbers, money or dates. This attribute is called the SQL Server Data Type. SQL Server provides us with a big library of system data types that define all types of data that can be used with SQL Server, from which we can choose the SQL Server data type that is suitable for the data we will store in that object. You can also define your own customized user defined data type using T-SQL script. SQL Server data types can be categorized into seven main categories:

Exact Numeric types, such as INT, BIGINT and numeric.
Approximate Numeric types, such as FLOAT and REAL.
Date and Time types, such as DATE, TIME and DATETIME.
Character Strings, such as CHAR and VARCHAR.
Unicode Character Strings, such as NCHAR and NVARCHAR.
Binary Strings, such as BINARY and VARBINARY.
Other Data Types, such as CURSOR, XML and Spatial Geography Types.

When two expressions with different SQL Server data types are combined together by an operator, they may not always play well together. They may be automatically, aka implicitly, converted into the suitable data type, making the generated combination better, without raising any errors. But you may face other cases, in which you need to manually, aka explicitly, perform a suitable data type conversion, in order to avoid the combination error. To perform the explicit data conversion, SQL Server provides us with three main conversion functions; CAST, CONVERT and PARSE. The main concern in this article is to compare the performance of these conversion methods and see which method is the best to use. Before going through the performance comparison between the three conversion functions, we will briefly describe these functions.

CAST

The cast function is an ANSI SQL-92 compliant function that is used to convert an expression from one SQL Server data type to another. You need to provide the CAST function with the expression that will be converted and the target data type that the provided value will be converted to. Being supported by ANSI SQL Standard, it is a preferable choice to convert between the different data types, especially when this conversion query will be executed on different DBMS such as Oracle or MySQL. The syntax that is used for the CAST function is shown below:

CAST ( expression AS data_type)

CONVERT

The CONVERT functions is a non- ANSI SQL-92 compliant function that is used to convert the provided expression datatype to another data type with formatting. You need to provide the CONVERT function with the expression that will be converted and the target SQL Server data type that the provided value will be converted to, in addition to the style. The style is an integer expression that specifies the format that the provided expression will be translated to using the CONVERT function, which is supported for some conversions, such as conversion between the text and date time values. Specifying the style helps in formatting the converted expression into a more readable format. The syntax that is used for the CONVERT function is shown below:

CONVERT ( data_type, expression [ , style ] )

PARSE

The PARSE function is used to convert the provided string expression into the requested SQL Server data type. You need to provide the PARSE function with the string value that will be converted, the target data type that the provided value will be converted to, in addition to the culture, which is an optional string that specifies the culture, any language supported by the .NET Framework, that the provided string value will be formatted in, with the current session language as the default culture value. The culture can be provided with the optional USING clause. The syntax that is used for the CAST function is shown below:

PARSE ( string_value AS data_type [ USING culture ] )

Performance Comparison

Let us see practically how we can use these three conversion functions, and the performance differences between it. For this purpose, we will create the below simple table and fill it with 20K records using the T-SQL script below:

USE SQLShackDemo

CREATE TABLE Conversion_Test

( ID INT IDENTITY (1,1),

EmpName NVARCHAR(50),

EmpID varchar(10),

AvgValue varchar(50),

EmpDate varchar(50)

)

INSERT INTO Conversion_Test VALUES ('AAA','758','87.44','2007-10-12')

GO 10000

INSERT INTO Conversion_Test VALUES ('BBB','685','77.11','2009-07-21')

GO 10000ue in the second row values

The table is ready now. In our demo here, we will perform the conversion operations on three columns; EmpID, AvgValue, and EmpDate. And for each column, we will do the SQL Server data type conversion three times, using the three conversion methods described previously. We will start with converting the EmpID column into INT data type using the CAST, CONVERT and PARSE commands after enabling the TIME statistics for performance comparison purposes using the T-SQL script below:

SET TIME STATISTICS ON

SELECT CAST(EmpID AS INT) FROM Conversion_Test;

SELECT CONVERT(INT,EmpID) FROM Conversion_Test;

SELECT PARSE(EmpID AS INT) FROM Conversion_Test;

The three commands will convert the EmpID column into INT successfully and will return the same result. Checking the TIME statistics result, you will see that the CAST conversion function has the least execution time and the PARSE function has the largest execution time, which is about 3 times the execution time for the CAST function, in addition to the large CPU time consumed by the PARSE function, opposite to the other conversion functions that have no CPU time consumed. The TIME statistics is shown below:

Let us now move to converting the AvgValue column into DECIMAL data type. Again, we will use the three SQL Server data type conversion functions described previously, as shown in the T-SQL script below:

SELECT CAST(AvgValue AS DECIMAL(10,2)) FROM Conversion_Test;

SELECT CONVERT(DECIMAL(10,2),AvgValue) FROM Conversion_Test;

SELECT PARSE(AvgValue AS DECIMAL(10,2)) FROM Conversion_Test;

You can derive the same previous result from the TIME statistics below; the CAST function is the fastest function to convert the provided expression into the DECIMAL data type, and the PARSE function is the slowest, which is 2 times the time consumed by the CAST function. Another thing to concentrate on is that the execution time for three functions increased when converting to DECIMAL data type compared with converting to INT data type. The TIME statistics is shown below:

What about converting to DATETIME SQL Server data type? Converting the EmpDate column to DATETIME data type using the three conversion functions:

SELECT CAST(EmpDate AS DATETIME) FROM Conversion_Test;

SELECT CONVERT(DATETIME,EmpDate) FROM Conversion_Test;

SELECT PARSE(EmpDate AS DATETIME) FROM Conversion_Test;

Extra overhead will be noticed from the execution time increase and the CPU time increase when converting to DATETIME data type using the three conversion functions, keeping the previous rank of the CAST function as the fastest and the PARSE as the slowest. What we need to concentrate on also here is the execution time of the PARSE function which is 12 times the time consumed by the CAST function, and the CPU time of it is about 120 times the CPU time consumed by the CAST function. The TIME statistics shown below tell you not to take the risk of using the PARSE function to convert into DATETIME data type:

Conversion Exception Handling

If you include the previous conversion functions within your script to perform SQL Server data type conversion operations that may fail due to data issue, the query will throw an error and fail, rolling back the parent transaction that it runs inside. SQL Server 2012 introduced new conversion functions that have the same functionality as the previous three converting functions, in addition to data type conversion exception handling mechanism to handle the errors generated by invalid conversions. The three newly introduced conversion functions are: TRY_CAST, TRY_CONVERT and TRY_PARSE. The functions try to convert the provided expression to the requested data type. If the conversion succeeds, it will return the expression value after conversion. If an error occurs, it will return NULL value. The statements used for the three new conversion functions, which is very similar to the previous ones, are shown below:

TRY_CAST ( expression AS data_type )

TRY_CONVERT ( data_type, expression [, style ] )

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Let us truncate the testing table and insert new two records; a normal record and another record with data that can’t be converted to the target SQL Server data types, using the T-SQL script below:

TRUNCATE TABLE Conversion_Test

INSERT INTO Conversion_Test VALUES ('AAA','758','87.44','2007-10-12')

INSERT INTO Conversion_Test VALUES ('AAA','758K','87.44i','2007-10-12F')

Performing the same previous data conversion of the three columns using the CAST, CONVERT and PARSE functions:

SELECT CAST(EmpID AS INT) FROM Conversion_Test;

SELECT CONVERT(INT,EmpID) FROM Conversion_Test;

SELECT PARSE(EmpID AS INT) FROM Conversion_Test;

SELECT CAST(AvgValue AS DECIMAL(10,2)) FROM Conversion_Test;

SELECT CONVERT(DECIMAL(10,2),AvgValue) FROM Conversion_Test;

SELECT PARSE(AvgValue AS DECIMAL(10,2)) FROM Conversion_Test;

SELECT CAST(EmpDate AS DATETIME) FROM Conversion_Test;

SELECT CONVERT(DATETIME,EmpDate) FROM Conversion_Test;

SELECT PARSE(EmpDate AS DATETIME) FROM Conversion_Test;

All conversion queries will fail;

converting the EmpID into INT will fail due to the ‘K’ character in the second row,
converting the AvgValue into DECIMAL will fail due to the ‘i’ character in the second row
and converting the EmpDate into DATE TIME data type will fail due to the ‘F’ character in the second row.

You can see that the whole conversation operations failed due to the data issue in the second-row values, although the first row values can be converted with no issue. The generated error message is shown below:

Trying to perform the conversion operations using the new conversion commands; TRY_CAST, TRY_CONVERT and TRY_PARSE commands, using the T-SQL script below:

SELECT TRY_CAST(EmpID AS INT) AS EmpID FROM Conversion_Test;

SELECT TRY_CONVERT(INT,EmpID) AS EmpID FROM Conversion_Test;

SELECT TRY_PARSE(EmpID AS INT) AS EmpID FROM Conversion_Test;

SELECT TRY_CAST(AvgValue AS DECIMAL(10,2)) AS AvgValue FROM Conversion_Test;

SELECT TRY_CONVERT(DECIMAL(10,2),AvgValue) AS AvgValue FROM Conversion_Test;

SELECT TRY_PARSE(AvgValue AS DECIMAL(10,2)) AS AvgValue FROM Conversion_Test;

SELECT TRY_CAST(EmpDate AS DATETIME) AS EmpDate FROM Conversion_Test;

SELECT TRY_CONVERT(DATETIME,EmpDate) AS EmpDate FROM Conversion_Test;

SELECT TRY_PARSE(EmpDate AS DATETIME) AS EmpDate FROM Conversion_Test;

The query will be executed successfully; the correct data in the first row will be converted successfully with no issue, and converting the second row’s data will return NULL due to the invalid data conversation error. In this way, the data conversion error will be handled within the code without stopping the overall script. The returned result from the previous script will be as shown below:

Conclusion

SQL Server provides us with different ways to convert the SQL Server data type of an expression into another data type. Starting from SQL Server 2012, three new functions are introduced, that can be also used for data types conversion, in addition to the data conversion exception handling mechanism that makes the data conversion process more optimal.

The available commands to perform that are; CAST, CONVERT and PARSE. In this article, we described briefly the differences between these three functions, how to use it and finally compare their performance. In our demo, that is performed using three different data types conversions, it can be found that CAST is the fastest function that can be used to convert the data type of the provided value, and the PARSE function is the slowest.

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Conclusion

Useful Links

Related posts: