Don’t Fear Dynamic SQL

One comment that I hear over and over again but that’s totally without merit is “The use of dynamic SQL is bad.” So what does “bad” really mean in this case? It can mean many things, depending on who made the comment. When used incorrectly, dynamic SQL certainly has some downsides. But when used properly, it has many positive attributes, including the ability to enhance performance in several ways, as you’ll see shortly. Let’s see if I can make a believer out of you.

What Does It All Mean?

To begin, let’s define two terms: dynamic SQL and ad hoc statement. Dynamic SQL occurs when one or more T-SQL statements are dynamically pieced together inside a stored procedure or T-SQL code block and executed with either the EXECUTE command (which can be shortened to EXEC) or the sp_executesql system stored procedure. An ad hoc statement is a T-SQL statement that’s built by a client application and executed directly as a batch call to SQL Server. However, for the most part, you can equate the ad hoc building of T-SQL statements to how the EXEC command is used.

I’ll explain the two methods for executing dynamic SQL in a little more detail, but keep in mind that both EXEC and sp_executesql are fully documented in SQL Server Books Online (BOL). Both methods are commonly used to dynamically build a statement based on a set of parameters passed into a stored procedure or a set of values obtained by querying metadata. To simplify things, I’ll use a series of variables to simulate a set of values.

First, let’s talk about the EXEC command. It has only one parameter, which is a string or variable that denotes the statement you want to execute, as shown in the example in Listing 1.

DECLARE @SQL NVARCHAR(500), @Columns NVARCHAR(100),  @Table NVARCHAR(128), @AddressID INT ;SET @Columns = 'City, StateProvinceID,PostalCode';SET @Table = 'Person.Address' ;SET @AddressID = 32 ;SET @SQL = 'SELECT ' + @Columns + ' FROM ' + @Table  + ' WHERE AddressID = ' + CAST(@AddressID AS NVARCHAR(10)) ;EXEC(@SQL) ;

The code in Listing 1 would be functionally equivalent to running this SELECT statement:

SELECT City, StateProvinceID,PostalCode  FROM Person.Address  WHERE AddressID = 32

The sp_executesql stored procedure takes three parameters in the following order: the statement itself, the parameter definition list, and the assignment of the values to the parameters defined in the list. (Again, see BOL for more details.) As Listing 2 shows, the code that uses sp_executesql looks similar to the code that uses the EXEC command, except you build a parameter in the WHERE clause instead of hard-coding the AddressID value.

SET @SQL = 'SELECT ' + @Columns + ' FROM '  + @Table + ' WHERE AddressID = @P1' ;EXEC sp_executesql @SQL, '@P1 INT', @P1 = @AddressID ;

The code in Listing 2 would be functionally equivalent to running this SELECT statement:

SELECT City, StateProvinceID,PostalCode  FROM Person.Address  WHERE AddressID = @P1

Forget What You Know

Now that I have shown you both methods, you should essentially forget about EXEC when it comes to executing statements dynamically. Sp_executesql is the preferred way, clear and simple, because you have the potential to reuse the query plans from previously executed statements by passing different values in the parameter list. Except for very simple cases, EXEC is unlikely to reuse a previously generated plan and will force SQL Server to compile a new one each time you call it with a different value.

Why All the Fuss?

Not reusing query plans can take its toll on system resources such as CPU and memory. Because compiling a query plan can be one of the most CPU-intensive operations, you want to minimize this as much as possible for peak performance. In addition, if you don’t properly parameterize statements, you can waste a lot of memory storing query plans that will only be used once. (The amount depends on how much memory SQL Server has available.) For more information about how the database engine utilizes the plan cache, see the white paper “Plan Caching in SQL 2008”  and the article “Fine-Tuning Plan Reuse."

I can’t stress the importance of plan reuse enough these days. Too many new development efforts utilize a framework that centers on the ad hoc building of T-SQL statements that are submitted to the database engine as a nonparameterized batch. Although this might make the development effort slightly easier, it negatively affects database performance and scalability.

Here Is the Difference

I can hear you saying that even when you use sp_executesql correctly, you’ll still get multiple query plans because the cores of the statements aren’t always the same. Yes, this is true, and there’s no getting around it. If you run the following two statements, you’ll get two plans in the cache:

SELECT City, StateProvinceID,PostalCode  FROM Person.Address  WHERE AddressID = @P1

SELECT TOP(@P2)TerritoryID  FROM Sales.Customer WHERE CustomerID = @P1

In a nutshell, anytime there’s a difference in any of the characters (including spaces) in the statement, you’ll get another plan. However, you’re very likely to call the same statement many times, each with a different value for one or more search arguments, just as you would if it were a stored procedure. And if you parameterize the applicable portions of the statement that will change with each call, you can avoid having to create a new plan for each one. You can’t parameterize the list of columns or the table names but you can indeed create parameters for the WHERE clause and aspects such as the TOP clause. Then you can execute these statements with sp_executesql and pass in the appropriate values for the parameters each time, allowing SQL Server to reuse the query plan from the first time it executed that statement.

The Choice Is Yours

My goal here isn’t to make you an expert on sp_executesql, as there are already many good resources you can use to achieve that goal. My goal is to call your attention to how important it is to think about using sp_executesql instead of executing a traditional ad hoc statement with EXEC. Time and time again, I’ve seen the mistake of not taking a little bit of extra time and effort upfront to properly parameterize the calls to SQL Server. As a result, when the database and application are scaled, they suffered from performance problems.

This was a very simplistic overview of dynamic SQL, but I hope it gets you thinking about how dynamic SQL can be a good thing for your system if done correctly. You should feel comfortable knowing that executing dynamically generated T-SQL statements using sp_executesql with parameters is virtually no different in terms of performance than if you executed a custom stored procedure with the same parameters. However, there can be a huge performance difference between executing dynamically generated T-SQL statements using sp_executesql with parameters and submitting total ad hoc statements with hard-coded values in the WHERE clause. The choice is yours, and so will be the rewards or the spoils.