TRY_CAST and TRY_PARSE Can Return Different Results

There’s a lot of guidance out there that states that TRY_CAST is a faster, more modern version of TRY_PARSE and that TRY_PARSE should only be used if you need to set the optional culture parameter. However, the two functions can return different results in some cases, even without the culture parameter.

Parsing

First I’ll throw a million rows into a single varchar column temp table:

CREATE TABLE #number_as_string (why_tho VARCHAR(100));

INSERT INTO #number_as_string (why_tho)
SELECT ISNULL(CAST(TRY_CAST(q.RN % 33000 AS SMALLINT) AS VARCHAR(100)), '')
FROM
(
	SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
	FROM master..spt_values t1
	CROSS JOIN master..spt_values t2
) q;

Let’s suppose that I want to count the rows that are numbers that fit within the limits of the TINYINT data type. The following code takes 36937 ms of CPU time on my machine and returns a value of 7935:

SELECT COUNT_BIG(*)
FROM #number_as_string
WHERE TRY_PARSE(why_tho AS TINYINT) IS NOT NULL
OPTION (MAXDOP 1);

The poor performance of the query isn’t unexpected. The documentation mentions the following about TRY_PARSE:

Keep in mind that there is a certain performance overhead in parsing the string value.

Casting

I can use TRY_CAST in an attempt to avoid the string parsing overhead of TRY_PARSE. The following code uses 110 ms of CPU time so it is significantly faster than the previous query:

SELECT COUNT_BIG(*)
FROM #number_as_string
WHERE TRY_CAST(why_tho AS TINYINT) IS NOT NULL
OPTION (MAXDOP 1);

However, this query returns a value of 14895, nearly double the count from before. The query results are different because TRY_PARSE returns NULL for an empty string but TRY_CAST returns 0. As a workaround (special thanks to Stephen Morris), the following query returns the expected value of 7935 and completes after using 157 ms of CPU time:

SELECT COUNT_BIG(*)
FROM #number_as_string
WHERE TRY_CAST(why_tho AS TINYINT) IS NOT NULL AND why_tho NOT IN ('', '+', '-')
OPTION (MAXDOP 1);

Final Thoughts

It’s not that TRY_CAST is returning the wrong results. In SQL Server, an empty string converts to 0 when cast to an integer data type. It’s more so that when TRY_CAST is applied to a string, the person writing the query usually wants only the values that a human would consider to be a number. TRY_PARSE seems to follow a set of rules that is more in line with human judgment of what is and isn’t a number. Avoiding TRY_PARSE due to the performance penalty is still a good idea in many cases, but be sure to filter out empty strings if you don’t want them included in the result set. If anyone knows of any other values treated differently between TRY_CAST and TRY_PARSE kindly leave a comment on this blog post. Thanks for reading!