Making TRY_CAST and TRY_CONVERT Queries Faster with Indexed Computed Columns


I know this is gonna sound crazy, but let’s say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let’s say your application frequently ran a query looking for dates in that column, like this:

SELECT * FROM dbo.Users WHERE TRY_CAST(DisplayName AS DATE) IS NOT NULL;

Even if you create an index on DisplayName, SQL Server ignores it because it believes so many rows are going to match, and it doesn’t want to do all the back & forth key lookups between the DisplayName