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:

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 index and the clustered index (to get SELECT *):

Yes, believe it or not, there are actually users in the Stack Overflow database whose names can be converted to dates:

So anyhoo, I’m not allowed to change the query, but I need to make it go faster. I have 3 problems:

1. The estimates are wrong, so
2. SQL Server refuses to use the DisplayName index (due to an artificially high number of key lookups)
3. SQL Server can’t seek specifically to the rows that match (it has to do the cast on every row)

I can fix problems #1 and #2 with a computed column, like I explain in the Artisanal Indexes module of my Mastering Index Tuning class:

Presto, the estimates are more accurate, and SQL Server is now doing less logical reads (because it’s scanning the DisplayName index rather than the entire clustered index, which includes all the columns.) Now, to solve problem #3, I would usually add a nonclustered index on our newly created column, but…

You might think, “Wait, how can a cast as a date be non-deterministic? Does it have something to do with the fact that it might sometimes return null?”

No, it’s because your session’s date format can cause the same string to return different dates. Here, I’m using the Books Online example for SET DATEFORMAT to set my default date formats to different styles, and then when I do a TRY_CAST on the same value, I get different dates:

Diabolical. So I can’t use an index on a computed column to make this thing crazy fast, and I’ll just have to settle for an index scan.

But TRY_CONVERT *does* work,
as long as you specify a format.

If I try this same trick with TRY_CONVERT and I specify the exact date format I want – obviously ISO 8601, as any XKCD fan will tell you:

SQL Server is willing to create a nonclustered index on that, AND it’ll automatically do an index seek on it rather than scanning & computing the whole thing. That’s awesome! However, if I try that same trick without a specific format, I’m back to being unable to create the index on it:

Dang. And don’t even get me started on filtered indexes with that stuff – that’s a whole ‘nother ball of wax. For that kind of thing, hit the Artisanal Indexes module.