About wildcards and data type precedence

Implicit conversions in SQL Server follow a specific, predictable order, called data type precedence. This means that if you compare or add/concatenate two values, a and b, with different data types, you can predict which one will be implicitly converted to the data type of the other one in order to be able to complete the operation.

I stumbled on an interesting exception to this rule the other day.

Consider the following example table of integers:

CREATE TABLE #integers (
    i       int NOT NULL,
    CONSTRAINT PK PRIMARY KEY CLUSTERED (i)
);

INSERT INTO #integers (i)
SELECT TOP (1000)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.messages;

Let’s look at an example:

DECLARE @s varchar(100)='123';

SELECT *
FROM #integers
WHERE i=@s;

In this query, data type precedence determines that @s will be converted to an int value, because int (the data type of the “i” column) has higher precedence than varchar. You can see the results of this conversion when we look at the resulting execution plan:

Now, because we’re looking for an int value in an indexed int column, SQL Server can use a Clustered Index Seek on that column. So what would happen if we change the WHERE clause from an equality condition to a wildcard?

DECLARE @s varchar(100)='123';

SELECT *
FROM #integers
WHERE i LIKE @s;

Turns out that with a LIKE comparison, both sides need to be string values, so in this case SQL Server will convert the “i” column to a varchar. The effect of this, however, is that this search predicate is no longer sargable, which means we need to scan through the entire table rather than seeking to a specific point in the index.

How about dates?

Let’s try something similar, but instead of integers we’ll look at date columns:

CREATE TABLE #dates (
    [Date]          date NOT NULL,
    [DateTime]      datetime2(7) NOT NULL,
    CONSTRAINT UQ_Date UNIQUE ([Date]),
    CONSTRAINT UQ_Datetime PRIMARY KEY CLUSTERED ([DateTime])
);

INSERT INTO #dates ([Date], [DateTime])
SELECT TOP (1000)
       DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2000-01-01'),
       DATEADD(minute, 123*ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), '2000-01-01')
FROM sys.messages;

Will an equality condition on a date column work the same way it did with an integer?

DECLARE @s varchar(100)='2000-01-02';

SELECT *
FROM #dates
WHERE [Date]=@s;

Yes. With an equality condition, data type precedence applies, and the varchar parameter is converted to date. We get a sargable search condition that allows us to use a Seek on the index. The same thing applies if we use a different date format in @s:

DECLARE @s varchar(100)='2000.01.02';

SELECT *
FROM #dates
WHERE [Date]=@s;

The reason for this is that SQL Server does not make any assumptions with regards to the format of the date in the @s variable: Note the third argument of CONVERT_IMPLICIT, which is “0”. This argument sets the string format for the conversion. For instance, 121 is the ISO standard yyyy-mm-dd, 110 is the US mm-dd-yyyy, and so on. A zero means “best guess”.

So what happens if we try a wildcard on a date or datetime column?

DECLARE @s varchar(100)='2000-01-02';

SELECT *
FROM #dates
WHERE [DateTime] LIKE @s+'%';

Turns out, this behaviour is consistent across data types.

Just like when we tried a wildcard condition with the int column, the datetime2 column is converted to a varchar, and then tested for the LIKE condition.

Formatting

Note that when SQL Server implicitly converts a date or datetime column to varchar, it does so using the 121 format for the conversion, which means that the date is represented as yyyy-mm-dd hh:mi:ss.mmm. This becomes important when you apply a string comparison. The following comparisons will work:

WHERE [DateTime] LIKE '2021-%' -- all 2021 transactions
WHERE [DateTime] LIKE '2021-02%' -- all February, 2021 transactions
WHERE [DateTime] LIKE '2021-02-03%' -- all February 3rd, 2021 transactions

The following will run, but won’t return any rows:

WHERE [DateTime] LIKE '%/%/2021%' -- all 2021 transactions
WHERE [DateTime] LIKE '2/%/2021%' -- all February, 2021 transactions
WHERE [DateTime] LIKE '2/03/2021%' -- all February 3rd, 2021 transactions

The reason being that the implicitly converted datetime string is in ISO format, so the strings won’t match the wildcard.

But please don’t implicit conversions.

Needless to say, even if you can get something like this to run and return correct results, please don’t. The better strategy is to avoid implicit conversions and create a sargable query.

WHERE [DateTime]>='2021-01-01' AND [DateTime]<'2022-01-01' -- all 2021 transactions
WHERE [DateTime]>='2021-02-01' AND [DateTime]<'2022-03-01' -- all February, 2021 transactions
WHERE [DateTime]>='2021-02-03' AND [DateTime]<'2022-02-04' -- all February 3rd, 2021 transactions

All three of these queries are sargable, will benefit from column statistics, and will result in lightning-fast Seek queries, rather than scanning the entire table and converting every date to a string before making a text comparison.

But wait, there’s more!

Turns out, as Dirk Hondong (b|t) pointed out in the comments, that there’s a difference between how SQL Server converts different date/time data types to strings. The newer date and datetime2 types will convert to the ISO “2021-02-03” and “2021-02-03 00:00:00.000” formats, respectively, while the legacy datetime type will convert to the traditional “Feb 3 2021 12:00AM”. I suppose this is for backward compatibility. Needless to say, the datetime data type is not recommended for new development.

Thanks, Dirk!