SQL Server Magazine

A number of other query elements preclude auto-parameterization. By default, SQL Server won’t autoparameterize queries containing any of these constructs:

• IN clauses
• BULK INSERT
• UPDATE statement with SET clause that contains variables
• A SELECT statement with UNION
• A SELECT statement with INTO clause
• A SELECT or UPDATE statement with FOR BROWSE clause
• A statement with query hints specified using the OPTION clause
• A SELECT statement whose SELECT list contains a DISTINCT
• A statement with the TOP clause
• A WAITFOR statement
• When a SELECT query contains a subquery or JOIN
• When a SELECT statement has GROUP BY, HAVING, or COMPUTE BY
• Expressions joined by OR in a WHERE clause
• Statements submitted via EXEC string
• INSERT... EXEC statement

In other words, SQL Server will autoparameterize only the simplest queries.

End of Article