A friendly reminder on SET ANSI_NULLS ON in stored procedures

Working with SQL Server and stored procedures is a well-known task to many developers. Recently, I needed to debug a slowly running stored procedure on a SQL Server 2008 database. I knew that the procedure used to run correctly (that is, fast enough) before small changes to the code, after which the delays started. However, the changes were very minor, and didn't alter the way the SELECT statements inside the procedure worked.

Indeed, the situation was rather puzzling, until I compared the full scripts of both the CREATE PROCEDURE and ALTER PROCEDURE statements. It turned out that the difference was in the SET ANSI_NULLS option: in the fast procedure, the option was ON, and in the new version it was OFF.

Why did the performance change? The reason is that this option changes the way NULL values are processed in queries, and depending on the database structure and queries executed, this can have a difference in the speed at which SQL Server can execute the procedure.

The default should be to always enable this option. But, the thing to remember is this: whenever you create your stored procedure or alter it, the SET ANSI_NULLS option must be set before the CREATE or ALTER procedure. Then, this information is associated with the procedure. Once this is done, you cannot anymore set the ANSI_NULLS option in client code, because it is an option tied to the procedure, not the caller. This is something not all .NET and SQL developers are aware of.

Finally, the future SQL Server versions (think SQL Server 2012 and beyond) will not support this option anymore, and will default to SET ANSI_NULLS ON behavior. Thus, it is best to migrate away from the non-standard functionality.