May 18, 2004

Stored Procs: DDL First, DML Later

SQL Server will recompile a stored procedure when a DML statement is executed after a data definition language (DDL) statement, where the DML references any object modified by the DDL. This happens because SQL Server needs to take into account the changes made to the object by the DDL in order to create a plan for the DML. If you take care of all your DDL at the start of the stored procedure, then it only has to recompile once. If you mix DDL and DML statements, you will force the stored procedure to recompile multiple times, and that will adversely affect your performance.