CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Under rare conditions, using IN clause can cause unexpected SQL behavior

Under rare conditions, using IN clause can cause unexpected SQL behavior

Rate This
  • Comments 1

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.

Leave a Comment
  • Please add 6 and 6 and type the answer here:
  • Post
  • Are you sure this doesn't happen in 2012? The page referenced lists 2 different CUs for it...

Page 1 of 1 (1 items)