Auto Parameterization and Implicit Conversion

When SQL Server processes a query that can be auto parameterized, the results can be a bit, shall we say, unexpected. Auto parameterization makes an implicit conversion to numeric(10,0) when there is a divisor present in the expression being evaluated. This conversion to numeric(10,0) can result in an unexpected loss of precision, and query results that differ compared to a non-autoparameterized query.

David Garrick as Richard III, by William Hogarth, circua 1745.

David Garrick as Richard III, by William Hogarth, circua 1745.

Take the following minimal, complete, and verifiable example. First, we’ll create a couple of tables:

Next, we’ll add a couple of rows:

This shows the rows present in each table:

The output:

╔══════════════════════╗
║        t1_id         ║
╠══════════════════════╣
║ 0.061803398874989670 ║
╚══════════════════════╝

╔══════════════════════╦═══════════════════════╗
║        t1_id         ║         t2_id         ║
╠══════════════════════╬═══════════════════════╣
║ 0.061803398874989670 ║ 42.000000000000000000 ║
╚══════════════════════╩═══════════════════════╝

Now, let’s run an auto-parameterized query:

The output contains zero rows!

The actual execution plan:

Plan showing auto-parameterization with implicit conversion to numeric(10,0)

Plan showing auto-parameterization with implicit conversion to numeric(10,0)

Notice the WHERE clause has WHERE [t1].[t1_id]=@1/@2 – the @1 and @2 indicate the query has been auto-parameterized. If you look at the query plan XML, you’ll see StatementOptmLevel="TRIVIAL" and the <ParameterList> section contains the following:

The plan clearly shows the auto-parameterized values have an implicit conversion to numeric(10,0), as seen in the highlighted section of the plan above. This implicit conversion is converting the value 0.061803398874989670 to 0.061803398874989, which returns no matching results.

However, if we explicitly parameterize the query, we get the expected results:

╔══════════════════════╗
║        t1_id         ║
╠══════════════════════╣
║ 0.061803398874989670 ║
╚══════════════════════╝

Similarly, if auto-parameterization is not available, for instance when we force a non-trivial query plan to be generated, the expected results are returned:

╔══════════════════════╦═══════════════════════╦══════════════════════╗
║        t1_id         ║         t2_id         ║        t1_id         ║
╠══════════════════════╬═══════════════════════╬══════════════════════╣
║ 0.061803398874989670 ║ 42.000000000000000000 ║ 0.061803398874989670 ║
╚══════════════════════╩═══════════════════════╩══════════════════════╝

The plan for the above query looks like:

Non-auto-parameterized query plan

Non-auto-parameterized query plan

As you can see above, the plan contains no implicit conversion, and returns the desired results.

This is admittedly a bit of an edge-case, but still worth knowing about. Microsoft has documented this behavior at their Docs page covering the int data type.

Thanks for reading this post, and please check out the rest of our posts on T-SQL!