Under rare conditions, using IN clause can cause unexpected SQL behavior
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.
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.