Do not mix (or interleave) DDL and DML statements in your stored procedures.
I see this all over the internet in best practice articles for coding T-SQL. What does this mean, and is it true? Let’s dive into this…
DDL = Data Definition Language. That means any statement that defines any data storage…all CREATE/ALTER TABLE, CREATE/ALTER INDEX statements are included here. DML = Data Manipulation Language. That means any statement that retrieves, changes, or stores data…all SELECT, INSERT, UPDATE, and DELETE statements are included here.
So, the best practice is that you shouldn’t mix these 2 types of statements in your stored procedure code. Or, put another way, place all of your DDL statements at the beginning of your procedure, then continue with your DML code. I would agree that this should be a best practice, but why? My answer is that first (and foremost as my testing below will show), it just makes your code more maintainable if all of your DDL statements and DECLARE statements are all together so that the next developer can find them more easily. Second, the placement of DDL statements in your procedure can affect the execution performance. As stated in many places on the internet, it can cause unnecessary recompiles of your stored procedure code, and that is something that we’d like to avoid as much as possible.
I’ve read a bunch of articles on this (most of them written for SQL 2005 or before) and I see some justification for this, but I wanted to test it out myself…on SQL 2014. I wanted to confirm what I read about mixing DDL and DML statements, and I want to test out some variations, including temp tables, table variables, and simple DECLARE statements.
My results show that for SQL 2014 the location of DDL statements within a stored procedure doesn’t really affect the number of recompiles. The best practice here is really based on good coding techniques and maintainability of the code. My general rule is that there shouldn’t be any DDL statements for permanent tables in stored procedures (for OLTP-type applications).
Test one: I wrote a simple stored proc that mixes CREATE TABLE statements with INSERT statements. The CREATE statements are issued for permanent tables. Here is my basic procedure (written against the AdventureWorks2014 database):
create procedure usp_MyDDLTest as create table dbo.table1 (BusinessEntityID int); insert into dbo.table1 (BusinessEntityID) select BusinessEntityID from Person.BusinessEntity; create table dbo.table2 (ContactTypeID int); insert into dbo.table2 (ContactTypeID) select ContactTypeID from Person.ContactType; drop table dbo.table1; drop table dbo.table2; go
I executed this proc; here are the results from SQL Server Profiler:
The results were as I expected, I see an SP:Recompile event issued for each INSERT statement. The database engine needs to do this because the structure of the referenced table in each INSERT has changed after the start of execution of my procedure. I am also tracing some SQL statement events, and you can see following each SP:Recompile event there is a SQL:StmtRecompile event. This tells me that the engine is now smart enough to perform a statement-level recompile instead of a full procedure recompile; this is more efficient than it used to be. Based on Microsoft technical articles I believe statement level compiles were introduced in SQL Server 2005. Test one confirms what I have read so far.
Test two: Put all DDL statements at the top. I moved the CREATE TABLE dbo.table2 statement to the beginning of my test procedure as suggested in the best practice. I executed it and here are my results:
Interesting…I still see 2 recompile events in my Profiler output. That tells me that Microsoft has changed the engine; moving my DDL statements to the beginning no longer cuts down on the number of recompiles. I’m guessing that, since the recompiles are now at the statement level, this reason for putting DDL together at the top of procedures no longer applies! Let’s move on…
Test three: Only create the tables if necessary. I added an IF NOT EXISTS statement to each CREATE TABLE and removed the DROP TABLE statements at the end of my procedure. With this code I should see 2 recompiles on the first execution of the procedure, but no recompiles on subsequent executions. And that’s exactly what I saw.
Since this post is getting a little long I won’t bore you with the rest of my testing results. I ran several more tests, testing temp tables, table variables, and simple DECLARE statements. I tested simple DECLARE statements because a few internet articles I read stated that DECLARE statements were, in fact, DDL, and cause re-compiles. My results? Contrary to what I read on the internet my tests (using SQL 2014, remember!) with temp tables yielded zero recompiles; this surprised me. Testing table variables and simple DECLARE statements, both mixed in the DML statements and at the beginning also both yielded zero recompiles.