Don’t Mix DDL and DML

I sat through an absolutely fantastic presentation on query optimization a couple of weeks back.  The presenter did a fine job of covering many of the basics like SET NOCOUNT ON, minimize cursor use, avoid explicit or implicit functions in the WHERE clause and why not to use the sp_ prefix when naming custom stored procedures.  He also touched upon practices which are critical aspects of query optimization, yet, in my opinion, few put into practice. Specifically, he talked about the need for benchmarks and how to establish them.  He also demonstrated how to conduct sufficient testing.  Then there were the more advanced topics like table fragmentation, associated DBCC commands, Statistics IO, Fill Factor and how procedure cache works.  He also shared plenty of TSQL samples and how they will (or will not) use a given index.  For example, did you know the LIKE clause can use indexes if the pattern starts with a character string such as WHERE lname LIKE ‘W%’ but LIKE cannot use an index if the pattern starts with a leading wildcard, such as WHERE lname LIKE ‘%wold’?  Again, excellent presentation and did I mention it was wrapped up in under an hour? 

I typically roll the dice with these types of presentation knowing they may turn out to be a refresher course, way over my head, or worse, an absolute bore.  This one was worth sitting through as I picked up the following nugget: Mixing DDL and DML Operations Can Cause a Recompile.

What does this mean?  DDL (Data Definition Language) and DML (Data Manipulation Language) almost always exists in our stored procedures.  Examples of DDL are our DECLARE variable and CREATE TABLE statements.  DML, on the other hand, includes our SET, SELECT and INSERT to name a few.  A key cause of stored procedure recompiles is the interleaving of DML and DDL statements — especially DDL following DML statements.  In other words, according to Microsoft best practices, place all of your DDL statements at the top of your stored procedures and then do your actual query work. Now, are recompiles always going to happen if you have interleaved DDL and DML?  Nope, not in all case, but you should do everything possible to prevent recompiles (even if it is super easy.)