Repercussions of Implicit Conversion

Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues.

Usually the downside from implicit conversion is seen through performance impact. But we’ve got something a little different today – an inconsistent result set.

Let’s demonstrate with a fictional query based on a recent issue I saw. The aim is to find clients with expired memberships:

SELECT ClientID
FROM dbo.Memberships
WHERE ISNULL(ExpiryDateTime, GETDATE()) < GETDATE();

Here’s the membership details in the table:

The expectation is that only Client 1 would be returned, and usually that’s what happens:

Usually? Yea, because sometimes we also get Client 3 returned too:

The reason for this? Well since the post is about Implicit Conversion, you can probably guess it’s that (well, sort of)

Implicit confusion

Why do the results differ? On the face of the query there’s nothing obvious. Hidden in the plan however is the implicit conversion:

The GETDATE() function returns a DATETIME value, however the ExpiryDateTime field is a SMALLDATETIME value. That’s why implicit conversion is kicking in to change the value used as the default to be a SMALLDATETIME too.

It’s being helpful, but missing the point.

The knock on result is that we’re comparing a SMALLDATETIME to a DATETIME data type. And these types have different precision.

A SMALLDATETIME is accurate to the nearest minute which will be rounded up or down when converting from a value which includes seconds. If GETDATE() returns 20:15:10, the default value would be 20:15:00, and at 20:15:35 it would be 20:16:00.

This conversion causes the change in behaviour depending on which second the query is ran. So sometimes the NULL field will be one side or the other of the current time and may (or may not) be returned.

Dealing with the issue

The fix? The best solution for implicit conversion is usually to remove it and deal with it ourselves. That means ensuring the correct data types are used where needed.

If we simply replace the implicit with our own explicit conversion, we have the same issue though. We need to evaluate the whole clause.

For example, one option to use a variable for the default value we can ensure that:

• It uses the correct SMALLDATETIME data type to avoid implicit conversion
• The same default value is used consistently on either side of the comparison

The result would look like this:

DECLARE @DefaultValue SMALLDATETIME = GETDATE();

SELECT ClientID
FROM dbo.Memberships
WHERE ISNULL(ExpiryDateTime, @DefaultValue) < @DefaultValue;

We now know that the comparison has the same data types on either side, and if we look at the plan again there’s no sign of implicit conversion:

Wrap up

In this post we’ve looked at how implicit conversion can innocently impact a query in an unusual way. The issue wasn’t the conversion itself, rather how it hid the fact that we were comparing data types with different precisions.

Removing implicit conversion from a plan isn’t simply a case of explicitly converting values. We need to understand where its happening and why, and the wider implications on the query.

Identifying this when designing a query can be key, and making any conversion explicit demonstrates there is a known disparity between the data types without needing to interrogate the query plan or schema.

Outside of plans, we can see implicit conversion warnings in tooling such as the sp_blitzcache proc. Whilst these may be harmless, its worth reviewing where and why its happening for high profile queries.

It’s not always performance which hurts, its simply easier to detect.