Under rare conditions, using IN clause can cause unexpected SQL behavior
Published Jan 15 2019 02:59 PM 181 Views
Microsoft
First published on MSDN on Feb 11, 2013
I want to make you aware of a latest SQL Server 2008 hotfix documented in http://support.microsoft.com/kb/2791745 .   Using large number of constants in IN clause can result in SQL Server termination unexpectedly.   When this happens, you won’t see anything in errorlog or any dumps generated by SQL Dumper.

The condition to trigger this is not that common.  Therefore, you may never experience this type of issue.     In order to hit this condition, you must have mismatched numeric data type in the IN clause.

Let’s assume that you have a table defined as “create table t (c1 numeric(3, 0))”.   But in the IN  clause, you have something like t.c1 in ( 6887 , 18663 , 9213 , 526 , 30178 , 17358 , 0.268170 , 25638000000000.000000 ).  Note that precision and scale of  the constants exceed the column c1’s precision and scale.

If your have queries like these, then you may experience this unexpected behavior depending on the final query plan.  This usually happens when you allow your user to do ad hoc queries and add random number of constant values which may exceed the column’s precision and scale.

Solution

The solution is to apply http://support.microsoft.com/kb/2791745 .  Note that the issue doesn’t happen on SQL 2012 and we are working on a fix on SQL Server 2008 R2 as well.

Version history
Last update:
‎Jan 15 2019 02:59 PM
Updated by: