Interleaving DDL and DML in stored procedure/trigger

It is not recommended to mix DML and DDL statement because recompilation issues may arise.

If DML and DDL statements are interleaved, stored procedures will often be recompiled instead of using the cached query plan, especially if there are DDL statements following DML statements. The risk of this happening is far less if you use table variables rather than creating temporary tables or ordinary tables.

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard

It makes for much clearer code and is easier to refactor if any DDL statements such as table creation or alteration are made at the start of the batch or procedure. However, only if you are using SQL Server before SQL Server 2005 do you need to worry about interleaving on performance grounds. This is because more recent versions of SQL Server no longer recompile the batch, instead of using the cached query plan, if DDL statements follow DML statements.

If either a user table, or temporary table, is created or altered within a batch, this triggers just a deferred statement-level recompile. This only happens the first time it is run, and if the version in cache is subsequently used, there is no more problem. A statement-level recompile will only recompile once, right before the batch executes for the first time.

DECLARE statements that produce a table variable do not cause any type of recompile when the procedure or trigger first executes, whereas the creation of a temporary table or ordinary table does. However, since table variables don't cause recompiles, the optimizer can never get accurate row count estimations for table variables, which can sometimes cause bad plans and poor performance.