Dynamic SQL: Sessions and Execution

I admit it – I do waaayyyy too much with dynamic SQL. But I just keep running into situations that require it. That being said, I ran into an interesting problem that had me puzzled. I found a bunch of different blog posts that pointed to me to the right direction but required a little extra work to find the solution.

There are several concepts that are at play here, so I’ll try to break this out so we can put the pieces together. The first once is centered around dynamic SQL. There are two parts of this I want to make sure we understand first – how it fits into sessions and how it gets executed.

Sessions

The way that dynamic SQL is executed in relation to the batch and session running it is pretty straightforward. All that the dynamic SQL does is create the statement that should be executed. When run, it’s executed as part of the same batch and session as the rest of the SQL.

Compare how executing dynamic SQL works with how execute something like xp_cmdshell works.

The results from the batch and dynamic SQL are the same (82) while xp_cmdshell returns a different value (74), even though it’s executed as part of the same batch.

This matters when it comes to things like tables or, more specifically, temp tables. A temp table can only be accessed by the session that created it and remains as long as that specific session is open. In comparison, a global temp table can be accessed by sessions other than the one that created it and remains as long as there is an open session using it. Based on the example above, xp_cmdshell can access a global temp table created by the batch but not a regular temp table. But since dynamic SQL is part of the session, dynamic SQL can be written to include regular temp tables.

This means if I want to insert into a temp table, I can add my insert to the dynamic SQL. Part of the reason I want to point this out is that I’m used to treating sp_executesql like any other stored procedure when I need to save the results of a proc into any table. The syntax I use is usually INSERT INTO TABLE … EXEC proc. But I can combine my insert into the table with the rest of my dynamic SQL statement.

This starts to matter when we start to think about how dynamic SQL is executed.

Executing Dynamic SQL

So let’s take a look at some examples combined with their estimated and actual execution plans.

Our first example is how I usually think of doing these statements, where the insert is outside of the dynamic SQL:

Here’s the sample execution from my notebook cell where the insert statement is outside of the dynamic SQL.

Now let’s take a look at the execution plans. First the estimated execution:

The estimated execution plan for the INSERT outside of the dynamic SQL. There are three parts to this – setting the variable, the insert into the temp table, then the execution of sp_executesql

The actual execution plan (aka – execution plan with realtime stats.) There’s only one query plan. But note that we don’t see what’s actually being done, just that we’re inserting into the temp table from sp_executesql. But there’s no insight to what the dynamic SQL is actually doing.

Here’s the second example – where we combine the INSERT statement with the dynamic SQL.

Here’s the sample execution from my notebook cell that ran after the initial statement.

Now let’s look at the estimated and actual execution plans:

The estimated execution plan. There are two plans as opposed to the three like the previous query. All we know is that sp_executesql is going to be called.

The actual execution plan (aka the execution plan with real time stats.) Because the insert is part of the dynamic SQL, we are getting the actual query plan. In fact, it may be even more useful because the plan isn’t hidden. I almost wish I realized this years ago…

Why does this behavior matter?

One of the reasons you may need to use dynamic SQL is to delay parsing of a statement. As we see from the execution plans, SQL Server doesn’t know what it’s executing until it’s executing. I’ve also used this in upgrade scripts where I need to reference a column that may not have been added yet so SQL Server doesn’t know it’s there yet.

Another reason you may want to defer the parsing is to help create better runtime error handling with TRY… CATCH logic. There are certain types of errors that can’t be caught so how that dynamic SQL is created could allow you to catch the errors and handle them properly.

Debugging dynamic SQL is always tricky because the more complex it is, the harder it is to figure out what failed and why. But understanding how it works in context of the session and how the code executes definitely comes into play down the road….

UPDATE: Want to read about the part of the solution? Check out Part 2!