Using indexed views? What is an imprecise or non-deterministic convert?

After last week’s post about using WITH (NOEXPAND) to query indexed views even on SQL Server Enterprise Edition, this week is a short but interesting side-road into deterministic values, and why it is important to get your data types correct. Longtime readers will know I care deeply about data types.

Here is a T-SQL query I wish to make into a view, and then index it because I want to join to the values in question:

SELECT
    ItemID,
    DateSold
FROM
    dbo.Item
WHERE
    DateSold > '1901-01-01';
ORDER BY ItemID;

The DateSold column in this table has the DATE data type, and there is a default value on this column, because the original designer of this table hates NULL values apparently. While I can’t blame them for trying to avoid adding a null bitmap to a table for whatever reason, it means that there’s now what we call a “magic string,” or default value that has a very specific meaning that is different from the obvious.

In other words, if we see a date in this table where the DateSold is 1900-01-30 (let’s call it a “magic date”), then the item has not been sold yet. This means regular date range queries won’t work unless we take this magic date into account.

Please don’t do this. Firstly, you’re lying to anyone who queries the table and is unfamiliar with the data. Secondly, if you don’t know what value should go there, use NULL which was invented for this kind of thing. I had a yellow sticky note on my desk when I worked at a bank that read “NULL is always unknown.” It’s a good mantra.

So now I need to exclude all items that are unsold, which means excluding anything dated 1900-01-30. As you can see in my query above, I chose 1 January 1901 as my start point, because 30 January 1900 is not round enough for my liking. I could just as easily have looked for the lowest legitimate value and picked a date before that.

When I create this view, I want to add an index on the ItemID so that I can join to it in other queries, which means I need to use the hint WITH SCHEMABINDING. What this does is enforce the structure of the underlying table I’m querying from. In other words, schema-binding prevents changes being made to that table until this view is dropped.

CREATE OR ALTER VIEW dbo.it_will_work_this_time_promise
WITH SCHEMABINDING
AS
SELECT
    ItemID,
    DateSold
FROM
    dbo.Item
WHERE
    DateSold > '1901-01-01';
GO

As we already know, we can’t use an ORDER BY clause in a view (even if there are “clever” hacks to do so, we should avoid them because they might affect our index), so we have to remove that. The view creates correctly and I can select from it, but as soon as I try to create my clustered index I get this wonderful message.

'Cannot create index on view "dbo.it_will_work_this_time_promise". The view contains a convert that is imprecise or non-deterministic.'

It’s referring to the WHERE clause, specifically my DateSold > '1901-01-01' condition. There’s an implicit conversion happening to make the string version of the date work with the DateSold column. To make it actually work as I intended, I need to convert the date into a deterministic value — an unambiguous date.

Using the magic of our favourite search engine and Microsoft Docs, we find ourselves on the CAST and CONVERT (Transact-SQL) page, and, if you’re like me, you’ve been here before and might even have it bookmarked.

On that page we need to cast (or convert) our date into an unambiguous value, which means eliminating any date or time style with 2-digit centuries. We’re left with a lot of options. While going through the list, we come to our old standby, ISO8601, except the conversion style is 23, so it’s probably not going to work. Sure enough, changing the WHERE clause to read WHERE DateSold > CONVERT(DATE, '1901-01-01', 23) results in the same error.

But wait! There’s an old standby that the SQL Server old guard like to use, and that’s yyyyMMdd (in other words, the year as four digits, and the month and day as two digits each, without any hyphens). I happen to remember that it’s style 112, which is easy to check against that list. If I had a 24-hour time as well, I would use 121.

As expected, this version of the query successfully allows me to create an index on the view, which in turn enables the use of WITH (NOEXPAND) as discussed in the previous post.

CREATE OR ALTER VIEW dbo.it_will_work_this_time_promise
WITH SCHEMABINDING
AS
SELECT
    ItemID,
    DateSold
FROM
    dbo.Item
WHERE
    DateSold > CONVERT(DATE, '19010101', 112);
GO

Leave your thoughts in the comments below.

Photo by Clint Adair on Unsplash.