In SQL Server, there are several conversion functions available that allow you to convert data from one datatype to another. In this article, we will discuss three new conversion functions introduced in SQL Server Denali: PARSE(), TRY_PARSE(), and TRY_CONVERT().

Difference between PARSE() and TRY_PARSE()

The PARSE() function attempts to parse a string and returns the value that is parsed from that string. However, if the PARSE() function encounters any incorrect values, it will throw an error. On the other hand, the TRY_PARSE() function performs the same parsing operation but instead of throwing an error, it returns the result as NULL if any incorrect values are encountered.

Difference between PARSE() and CONVERT()

While both PARSE() and CONVERT() functions are used for data conversion, they have different behaviors. The PARSE() function tries to parse a string and returns the value that is parsed from that string, if it can. On the other hand, the CONVERT() function tries to convert the string to a specified datatype. If the conversion fails, it will return an error.

For example, let’s consider the following scenario:

SELECT PARSE('100.000' AS INT) AS PARINT;
SELECT CONVERT(INT, '100.000') AS CONINT;

In this case, the PARSE() function successfully parses the string ‘100.000’ and returns the integer value 100. However, the CONVERT() function fails to convert the string to an integer and returns an error.

It’s important to note that PARSE() uses CLR (Common Language Runtime) datatype under the hood and currently can only convert a string to INT and DATETIME. On the other hand, CONVERT() works with any compatible datatype and can also be used to format dates.

Using PARSE() with Disabled CLR

If the CLR has been disabled using the sp_configure command, the PARSE() function will still work as it is a system function and not a user-defined function. However, it requires the .NET framework to be installed on the server.

Optimal Choice: PARSE() or CONVERT()

Both PARSE() and CONVERT() functions have different usages and it depends on your specific requirements. It is recommended to try both functions and use the one that gives you the correct result. In terms of performance, both functions have similar execution plans and IO reads.

For example:

USE AdventureWorks2008R2;

SELECT SP.[StateProvinceCode], PARSE(A.[PostalCode] AS INT) AS PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US' AND LEN(A.[PostalCode]) <= 6;

SELECT SP.[StateProvinceCode], CONVERT(INT, A.[PostalCode]) AS PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US' AND LEN(A.[PostalCode]) <= 6;

In the above example, both queries have the same execution plan and IO reads.

If you have any further questions about conversion functions in SQL Server, feel free to ask and I will expand this article accordingly.