Here’s an interesting observation I made when responding to one of the forums/Twitter feeds yesterday. As most of you know, SQL Server 2012 introduced a couple of new T-SQL functions. We will focus our attention to two of these today:

• TRY_CAST
  
• TRY_CONVERT

The basic functionality of both functions is the same – to check if a supplied value in one data-type can be successfully CAST/CONVERT to another data-type. Both functions return a value cast to the specified data type if the cast succeeds; otherwise, return null. This begs the question – What’s the difference between the two functions?

From a syntax perspective, both TRY_CAST and TRY_CONVERT have parameters similar to the CAST and CONVERT functions respectively. To demonstrate the functionality, I have taken the example from Books On Line as the base and modified it a bit:

/* STEP 01 - Demonstrate the functionality of TRY_CAST v/s TRY_CONVERT */
USE tempdb;
GO
SET NOCOUNT ON;

SELECT 
    CASE WHEN TRY_CAST('test' AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CAST(2013 AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS IntToFloat;
GO

SELECT 
    CASE WHEN TRY_CONVERT(float, 'test') IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CONVERT(float, 2013) IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS IntToFloat;
GO
/********************
RESULTS
********************/
/*
CharToFloat IntToFloat
----------- --------------
Cast failed Cast succeeded

CharToFloat    IntToFloat
-------------- -----------------
Convert failed Convert succeeded
*/

As can be seen from the results above, no error/exception was thrown during a failed CAST/CONVERT attempt. Our code was gracefully able to handle the error.

Now, let us study a couple of interesting points about these new functions:

TRY_CAST is NOT a keyword, TRY_CONVERT is

Colours are some of the most expressive elements of any user interface. If we look at the query editor window in the SQL Server Management Studio, we see that TRY_CONVERT is shown the default pink colour to indicate a keyword (in SQL 2012) whereas TRY_CAST is not indicated as a keyword.

Even Intellisense tells us that “’TRY_CAST’ is not a recognized built-in function name.”

TRY_CAST works with all compatibility levels on SQL 2012, TRY_CONVERT needs 110

A simple test shows us that TRY_CAST works with all compatibility levels supported on SQL Server 2012. However, the TRY_CONVERT keyword requires a compatibility level of 110.

USE master;
GO
--Safety Check
IF DB_ID('TRY_CAST_CONVERT_Testing') IS NOT NULL
BEGIN
    ALTER DATABASE TRY_CAST_CONVERT_Testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TRY_CAST_CONVERT_Testing;
END
GO

CREATE DATABASE TRY_CAST_CONVERT_Testing;
GO

--Let's take the database to the lowest supported level
ALTER DATABASE TRY_CAST_CONVERT_Testing SET COMPATIBILITY_LEVEL = 90;
GO

USE TRY_CAST_CONVERT_Testing;
GO
--Now, create a table:
CREATE TABLE dbo.TRYCASTCONVERT (AttemptId INT,
                                 FailingValue VARCHAR(10),
                                 PassingValue INT
                                );
GO
-- Insert some test data
INSERT INTO dbo.TRYCASTCONVERT (AttemptId, FailingValue, PassingValue)
VALUES (1, 'test', 7);
GO

USE TRY_CAST_CONVERT_Testing;
GO
--Execute the test
SET NOCOUNT ON;

SELECT 
    CASE WHEN TRY_CAST(FailingValue AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CAST(PassingValue AS float) IS NULL 
         THEN 'Cast failed'
         ELSE 'Cast succeeded'
    END AS IntToFloat
FROM dbo.TRYCASTCONVERT;
GO

SELECT 
    CASE WHEN TRY_CONVERT(float, FailingValue) IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS CharToFloat,
    CASE WHEN TRY_CONVERT(float, PassingValue) IS NULL 
         THEN 'Convert failed'
         ELSE 'Convert succeeded'
    END AS IntToFloat
FROM dbo.TRYCASTCONVERT;
GO

--Cleanup
USE master;
GO
IF DB_ID('TRY_CAST_CONVERT_Testing') IS NOT NULL
BEGIN
    ALTER DATABASE TRY_CAST_CONVERT_Testing SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TRY_CAST_CONVERT_Testing;
END
GO

/***********************
RESULTS
***********************/
/*
CharToFloat    IntToFloat
-------------- --------------
Cast failed    Cast succeeded


Msg 195, Level 15, State 10, Line 3
'TRY_CONVERT' is not a recognized built-in function name.
*/

The result is that while the call to TRY_CAST succeeds, the call to TRY_CONVERT returns an error – Msg 195 – ‘TRY_CONVERT’ is not a recognized built-in function name.

TRY_CAST can work on Azure as well, TRY_CONVERT can’t

This one should not come as a surprise at all. Because CAST is an ANSI standard (and therefore available in Azure),  the corresponding exception-handled version (TRY_CAST) would be easier to wire up in the Azure platform.

Internal implementation – TRY_CONVERT is implemented as TRY_CAST

We know that in Microsoft SQL Server, CAST is internally implemented as a CONVERT. Keeping the Include Actual execution plan option on (Ctrl+M), I conducted a test for TRY_CAST and TRY_CONVERT.

USE AdventureWorks2012;
GO
SET NOCOUNT ON;

SELECT 
    CASE WHEN TRY_CONVERT(float, ProductNumber) IS NULL 
    THEN 'Convert failed'
    ELSE 'Convert succeeded'
END AS Result
FROM Production.Product;
GO

As can be seen from the properties window, contrary to our observations about CAST and CONVERT, we realize that TRY_CONVERT is actually implemented as a TRY_CAST internally!

The remaining question…

With these observations, I now have a final question:

Why do TRY_CAST and TRY_CONVERT exhibit a behaviour opposite to that of CAST and CONVERT?

Do leave your thoughts in the blog comments as you continue with the day.

Further Reading

• CAST v/s CONVERT [Link]
  
• TRY_CAST [Link]
  
• TRY_CONVERT [Link]

Until we meet next time,

Be courteous. Drive responsibly.