Dynamic Execution Enhancements

Editor's note: The features described here aren't final, nor are they guaranteed to be in the final product release in the same form or at all.

Dynamic execution in SQL Server 2005 lets you dynamically construct and invoke T-SQL code. You might want to dynamically construct your code because some elements of static T-SQL code-such as object names and a query's WHERE or ORDER BY clauses-can't be replaced with variables. Dynamic execution in SQL Server 2000 has several limitations. For in-depth coverage about how dynamic execution works in SQL Server 2000, read SQL Server MVP Erland Sommarskog's article "The Curse and Blessings of Dynamic SQL" at http://www.sommarskog.se/dynamic_sql.html. In this column, I show you two areas in which SQL Server 2000's dynamic execution is limited, then I cover the enhancements SQL Server 2005 introduces to address those limitations.

Support for Longer Dynamic T-SQL Strings

SQL Server 2000 limits the size of a string that you can dynamically execute. SQL Server Books Online (BOL) says that the sp_executesql command accepts dynamic code as an argument called @stmt. BOL also says that the @stmt input should be either a Unicode constant or a variable that's convertible to the ntext data type. Ntext can reach up to 1 million characters (2GB), so it doesn't seem like your input code string is limited in size; however, you can't declare a local variable of the ntext data type in SQL Server 2000. Usually, when you need to construct T-SQL code dynamically you declare a variable and use various flow elements, such as loops and conditions, to construct the code within the variable. Because you can't declare the local variable as an ntext data type, you're limited to the largest data type that is not a large object (LOB). And because sp_executesql expects a Unicode input, you're limited to 4000 characters-the nvarchar data type's limit. But, if you use the EXEC command to invoke code dynamically, you can use regular characters instead of Unicode, which means that a variable's limit becomes 8000 characters. Plus, the EXEC command supports a special syntax that lets you concatenate variables. The following code shows how to concatenate the variables that have a maximum size:

EXEC(@var1 + @var2 + … + @varn)

When you dynamically construct your code by using the EXEC concatenation technique, you'll find it awkward and frustrating.

To address this problem, SQL Server 2005 introduces a MAX specifier for variable length data types, such as varchar, nvarchar, and varbinary. The MAX specifier tells SQL Server that you want the data type's maximum supported size, so when you declare a column or variable with the MAX specifier, you can use the column or variable both as a regular value and as a large object. SQL Server determines how to store the variable internally based on efficiency algorithms that you can't control. The MAX specifier's greatest benefit is that it lets you use a unified-programming model for small and large data. Unlike text, ntext, and image, you can use data types that have the MAX specifier in most of the functions that support regular data types as arguments and even as local variables.

You can use nvarchar(MAX) as the input for sp_executesql and your limit is 1 million characters. For example, Listing 1 constructs and executes code that contains 2000 lines of PRINT statements with increasing sequential integers. The @sql variable in the code has the length of 26,893 characters. If you run Listing 1, you'll see that it executes successfully and prints the length of the code and all 2000 numbers in the range 1 through 2000.

Support for Dynamic Pass-Through Code

SQL Server 2000 supports pass-through code with the OPENQUERY() table-valued function. The function returns a result table when you provide a linked server's name and a query string as input. The advantage OPENQUERY() has over other methods of invoking code on a remote server is that you submit pass-through code through OPENQUERY(), which means that you use the remote server's dialect instead of the local one. For example, if you issue an OPENQUERY() statement to a Microsoft Access database, you use the Access-SQL dialect. All other options, including querying linked servers directly by using OPENROWSET(), require that you use T-SQL code. However, OPENQUERY() has several limitations: For example, you can't dynamically construct the query string, and the query string can't use variables, which limits you when you need to invoke code that is based on arguments you accept in a stored procedure. Another limitation is that because OPENQUERY() is a table function, you must get a relational result, which means that the function is specified in the query's FROM clause. If you want to submit data-modification operations or Data Definition Language (DDL) operations to a linked server through OPENQUERY(), you have to make sure that the last activity returns a table, which can be awkward. For nonquerying activities, you can invoke sp_executesql on the linked server; however, you can do that only if the linked server is a SQL Server.

SQL Server 2005 enhances the EXEC command to let you dynamically issue pass-through code on a linked server that uses the target server's dialect (not necessarily SQL Server), supports arguments, and doesn't require the code to return a relational result set. The syntax for the enhanced EXEC command is simple: Specify a question mark in the code to represent an argument, then after the code string specify the argument's values in left-to-right order. The order you specify depends on the OLE DB provider you use to access the linked server, but most providers use left-to-right order for binding variables. Following the EXEC command's parenthesis, specify a linked_server_name. Before I show you an example for using the EXEC command, I first define a linked server that points to an instance called matrixs1 by running the following code:

EXEC sp_addlinkedserver [matrixs1], 'SQL Server';

Listing 2 returns the input product's attributes from the Product table in the AdventureWorks database in the linked server. A question mark represents the input argument in the code and assigns the constant 3. Table 1 shows the result which contains the product id, name and list price of product 3. Notice that you're not limited to constant inputs. You can revise Listing 2 to utilize a variable or an input parameter as Listing 3 shows. Even the dynamic code doesn't have to be a constant string; you can dynamically construct the code. Listing 4 revises the code from Listing 3 to construct and execute the code string in the @sql variable. And, you don't have to return a relational result. Listing 5 submits code to the linked server that creates the T1 table. All the previous examples issued code against a linked server to SQL Server. As I mentioned earlier, the pass-through code uses the target server's dialect. To demonstrate using a non SQL Server dialect, Listing 6 creates a linked server against an Access Northwind database and turns on the rpc out server option. Listing 7's EXEC command invokes a TRANSFORM query, which generates a dynamic PIVOT. The query creates a crosstab of Orders that has order years on rows, order months on columns, and number of orders in the intersection, as Table 2 shows.

Flexible Dynamic Execution

SQL Server 2000 programmers and DBAs struggle with dynamic execution limitations because they currently have to write complex lengthy code for some tasks involving dynamic execution. Two such limitations are the short supported size of the dynamic code string in practice and the fact that pass-through code does not accept arguments. Now you know how SQL Server 2005 will address them. SQL Server 2005 addresses the limitation on the input code string size by using the variable-length data type's MAX specifier, which lets you use a unified programming model for small and large data types. Enhancements to the EXEC command address the limitations on pass-through code. Dynamic execution in SQL Server 2005 is richer and more flexible allowing you to change cumbersome lengthy and complex code to short and simple code.