T-SQL Dynamic Query (4)--Dynamic SQL

Next: T-SQL Dynamic Query (3)--Static SQL

Objective:

There's a lot of talk about dynamic queries, and this article describes some of the ways to use dynamic SQL to solve dynamic queries.

Why use Dynamic sql:

In many projects, dynamic SQL is widely used and even misused, and in many cases, dynamic SQL is really the preferred method to solve many requirements. However, if used unreasonably, it can result in performance problems and cannot be maintained. Dynamic SQL, in particular, has its own pros and cons, and is used to evaluate the analysis:

Dynamic SQL Benefits:

• Dynamic SQL provides a powerful extension that can handle complex requirements, even when demand increases, and does not lead to linear growth in code as demand increases.
• Execution plans can cache query strings, meaning that most query conditions can reuse the execution plan cache without causing unnecessary recompilation.

Dynamic SQLDisadvantages:

• Unreasonable coding can cause code maintenance to get bogged down.
• Dynamic SQL is used to deal with higher-level problems, which can become overkill for simple problems.
• The test of dynamic SQL is obviously more difficult than other code, especially for the final execution of the statement, and it is easy to get a syntax error due to the nonstandard encoding.
• With respect to the previous option (RECOMPILE), dynamic SQL needs to take into account permission control.
• The plan cache for dynamic SQL is not always what you think, sometimes because of the input parameter values that result in a different plan generation.

Static SQL can actually handle most of the daily requirements, but as the need increases, static SQL becomes more complex and can lead to excessive recompilation, and dynamic SQL should be considered at this time.

Introduction to Dynamic SQL:
Overview:

In SQL Server, dynamic SQL can be implemented in three ways:

1. T-SQL stored procedures
2. CLR Stored procedures
3. Client statements

This article highlights stored procedures in T-SQL. There are two ways to handle user input:

• The parameters are implemented by concatenation of strings, such as: ' and col = ' + convert (varchar, @value) '
• Using sp_executesql for parameterized queries, you can turn the above parameters into: ' and col = @value '

For many reasons, the second method, sp_executesql, is recommended for everyday use. However, it is necessary to remind that the above mentioned three ways to implement dynamic SQL are not inherently good and bad, only according to the actual situation will be the most effective. This article will use the requirements in static SQL as a demonstration, for different query criteria, different sorting, or even different summary requirements.

Permissions:

For static SQL in stored procedures, there is no problem with permissions. As long as the caller of the stored procedure and the owner of the table are the same, the stored procedure can be executed without hindrance due to the ownership chain (ownership chaining,https://msdn.microsoft.com/zh-cn/library/ms188676.aspx). However, there is no ownership chain in dynamic SQL, even if you put them in stored procedures, because dynamic SQL has its own permission scope.

If you create dynamic SQL in a client program or CLR stored procedure, you also need to grant the user additional Select permissions on the tables, views, and custom functions that are involved in the query. Depending on the client program and the CLR stored procedure, the permission chain can be very confusing and out of control. However, you can use the following two ways to cope:

1. Create a certificate that is used to sign the stored procedure with this certificate. Then use the certificate to create a user, and because the user needs the SELECT permission.
2. Add execute as ' user ' in the stored procedure. The SELECT permission is then granted.

Parameterized query format for dynamic sql:

This section uses the template mentioned in the first article to make a transformation presentation, in order to be able to describe clearly, using the blog's own line number to label:

Use [Adventureworks2008r2]go CREATE PROCEDURE [dbo].                                                          [Sp_get_orders] @salesorderid int = NULL,                                                          @fromdate datetime = NULL,                                                          @todate datetime = NULL,         @minprice money = NULL, @maxprice money                                                          = NULL, @custid int = null,                                                          @custname nvarchar (+) = NULL, @prodid int = NULL, @prodnam E        nvarchar (+) = NULL, @employeestr varchar (MAX) = NU                                                       LL, @employeetbl intlist_tbltypereadonly,                                                          @debug bit =0                                                                                                                                                                                                  As                                                                     DECLARE @sql nvarchar (MAX),                                                                     @paramlist nvarchar (4000),                                                                                                                                                @nl char (2) = char (+) + char (10)                                     SELECT @sql = '                                                 SELECT O.salesorderid, O.orderdate, OD. Unitprice,od. OrderQty, C.customerid, per. FirstName as Customername,p.productid, p.name as ProductName, per.                                                                  BusinessEntityID as Empolyeeid from Sales.SalesOrderHeader o INNER JOIN sales.salesorderdetail od on o.salesorderid= od.                                     SalesOrderID INNER JOIN sales.customer C on O.customerid =c.customerid INNER JOIN Person.person per onc. Personid=per. BusinessEntityID INNER JOIN production.product p on P.productid =od.                                                                                                                                                       ProductID WHERE 1=1 ' [email protected]                        IF @salesorderidIS not NULL SELECT @sql + = ' and [email protected] ' + ' [Email pro                                                                                                                                        Tected] ' [email protected]                                                                            IF @fromdateIS not NULL                                                                                                                                            SELECT @sql + = ' and o.orderdate >= @fromdate ' [email protected] IF @todateIS Not NULL SELECT @sql + = ' and O.orderdate <                                                                                                                  = @todate ' [email protected]                            IF @minpriceIS not NULL SELECT @sql + = ' and OD.                                                                                                                                         UnitPrice >= @minprice ' + @nl                                                                            IF @maxpriceIS not NULL SELECT @sql + = ' and OD.                                                                                                                                         UnitPrice <= @maxprice ' + @nl                                                                              IF @custidIS not NULL                                                                SELECT @sql + = ' and O.customerid = @custid ' +                                                                                                                                  ' and C.customerid = @custid ' [email protected]            IF @custnameIS not NULL [Email&nbs P;protected] + = ' and per.                                                                                                                             FirstName like @custname + '% ' + @nl                                                                              IF @prodidIS not NULL [email protected] + = ' and OD. ProductID = @prodid ' + ' and P.productid = @prodid ' [email&                                                                                                                                               Nbsp;protected]                                                                            IF @prodnameIS not NULL                                                                                                           [email protected] + = ' and p.name like @prodname + '% ' + @nl                          IF @employeestrIS not NULL [email protected] + = ' and per. BusinessEntityID in ' + ' (SELECT number from dbo.intlist_to_                                                                                                                   TBL (@employeestr)) ' + @nl IF EXISTS (SELECT * from @employeetbl) [email  protected] + = ' and per.                                                                                                             BusinessEntityID in (SELECT val from @employeetbl) ' + @nl                                                                                                                                                   SELECT @sql + = ' ORDER Byo.salesorderid ' + @nl IF @debug                                                                          = 1             PRINT @sql                                                                         SELECT @paramlist = ' @salesorderid int,                                                                           @fromdate datetime,                                                                           @todate datetime, @minprice Money, @maxpr                                                                           Ice money, @custid nchar (5),                                                                       @custname nvarchar (40),                                                                                @prodid int,   @prodnamenvarchar (+), @employeestr varchar (MAX),                                                                                                                                        @employeetbl Intlist_tbltype READONLY ' EXEC [email& Nbsp;protected], @paramlist, @salesorderid, @fromdate, @todate, @minprice, @maxprice, @custi D, @custname, @prodid, @prodname, @employeestr, @employeetbl

Code Analysis:

In line 18th of the preceding code, a variable @sql is defined to store the query string. Because sp_executesql requires that the parameter must be nvarchar, nvarchar (MAX) is used here so that it is sufficient to hold all the final strings.

In line 20th, a variable @nl is used to implement line break functionality on Windows by assigning a value of char (+char) (10). Although it is a variable, it is actually a constant in the stored procedure.

In line 22nd to 31st, the core part of dynamic SQL is included and stored in the @sql variable. The whole dynamic SQL query is realized through the subsequent parameter stitching. Note that two names are used in the code (that is, the schema name) because, for performance reasons, SQL Server needs to locate objects precisely when compiling and optimizing, and if Table a exists with both the DBO.A and SALES.A schema names, the SQL Server needs to take the time to determine which table to use, which will bring a lot of overhead, note that even if only dozens of milliseconds, but for a frequently executed stored procedure or statement, the overall performance will be significantly lower, so whether based on performance or programming specification considerations, should bring the schema name, Of course, if your system has only dbo this default schema, does not take the line, but it is recommended to standardize the programming to improve readability and maintainability. Here again, in my optimized code, often see a lot of statements, table names using aliases, but in on, where and not with the alias prefix, I look very difficult to know the field from which table, to a related table to check, spend the time not to spend, in order to maintain the code of the People, You can do it.

In line 31st is a "where 1=1", similar to the placeholder in the programming language, so that WHERE the statement even if there is no error, the following will explain why also add @nl.

Starting at line 33rd, checks for all single-valued query parameters, and if the argument is not NULL, it is added to the corresponding column of the final SQL string. From here, pay attention to the use of single quotes, double quotes, and pay attention to adding @nl after each stitch.

In line 67th, the @employeestr parameter is processed in the same way as the previous static SQL. The rest of the remainder is relatively simple and does not explain too much.

In line 72nd, a parameter @debug is added, the default is 0, when the user calls in 1 o'clock, the output SQL string, which is useful when debugging and checking for errors, because dynamic SQL is often difficult to see the final statement directly from the code, if the development process is not aware of the quotation marks, spaces, type conversions, etc. will be the error during subsequent calls. With the @debug parameter, you can print out the statements that need to be executed without executing the statement (that is, before the error is stopped), noting that the order is important and that you may not be able to print it if you want to print after the error is executed.

For almost every line added after the @nl, of course, there is intent, if not add line characters, the code may become a single long string, print out of the visual. Even looks very painful, although now has the format tool, but is not every time to crack the success, to the single string of the beautification still is a waste of time.

Finally, execute the SQL string through sp_executesql, which is a system stored procedure that requires two fixed parameters, the first is the SQL string, and the second is the parameter column. These parameters must be of type nvarchar. In this example, the call statement is inside the stored procedure. You can also call the stored procedure externally. However, it is necessary to remember that dynamic SQL does not know any invocation parameters.

Note that the last parameter column of the stored procedure, @paramlist, is static, that is, the parameter set is fixed, even though some parameters are not always used.

Test:

You can test the stored procedure using the following statement:

EXEC [sp_get_orders] @salesorderid = 70467EXEC [Sp_get_orders] @custid  = 30097EXEC [Sp_get_orders] @prodid  = 936EXEC [sp_get_orders] @prodid  = 936, @custname = ' Carol ' EXEC [sp_get_orders] @fromdate = ' 2007-11-01 00:00:00.000 ', @todate = ' 2008-04-18 00:00:00.000 ' EXEC [sp_get_orders] @employeestr = ' 20124,759,1865 ', @custid = 29688 DECLARE @tbl intli St_tbltypeinsert @tbl (val) VALUES (20124), (759), (1865) EXEC [sp_get_orders] @employeetbl = @tbl, @custid = 29688

For such cases, all parameters need to be tested and it is best to know which parameters are used most frequently in the actual use.

Compilation and caching of Dynamic SQL:

The execution plan is reused every time the user invokes the stored procedure with the same query parameter set. If you call the previous chapter of a stored procedure sp_get_orders_1, such as:

EXEC [email protected] = 70467EXEC [email protected] = 70468EXEC [email protected] = 70469

Because of option (RECOMPILE), no execution plan is cached and is recompiled every time. However, for the stored procedures in this article:

EXEC [sp_get_orders] @salesorderid = 70467EXEC [sp_get_orders] @salesorderid = 70468EXEC [Sp_get_orders] @salesorderid = 70469

Only the first call is compiled and the execution plan is cached, and subsequent two calls will be run directly using the first execution plan. But when the parameters of the call change, such as:

EXEC [sp_get_orders] @salesorderid = 70467, @prodid  = 870

A new compilation occurs and a new cache entry is generated, but the original execution plan used to query the SalesOrderID is unaffected.

Special Query conditions:

In the previous static SQL, it has been shown how to implement some special query conditions with static SQL, this section will demonstrate the use of dynamic SQL to accomplish these tasks, as mentioned earlier, static SQL for simple query conditions, enough to cope with, but when the number and complexity of the requirements gradually increased, Static SQL will become uncontrolled. You need to consider dynamic SQL at this point.

Uneven distribution of data:

In many systems, a common case is that there is a status column on the order table with 4 values: N (New Order), P (processed), E (Exception order), C (processed order), and almost 99% of the data is C.

In this case, you can use the filtered index/filter index (FILTERINDEX) of the C value in the column to filter for unnecessary data or data that needs to be queried frequently. But if you write this in dynamic sql:

IF @status is not NULL  SELECT @sql + = ' and O.status = @status '

Since the execution plan for dynamic SQL is optimized for all scenarios, this is not specifically intended for filtering, and additional operational logic is required to "instruct" the optimizer to use this filtered index, such as:

IF @status is not a NULL   SELECT @sql + = ' and O.status = @status ' + case when                  @status <> ' C ' then                        ' and O. Status <> ' C ' ' ELSE '                  END

This is true for single-valued parameters, and if @status is multi-valued, that is, users need to filter some types of data, they need to add more processing logic in this way.

Custom sort:

In dynamic SQL, a common application is to use a custom collation to sort the result set by the sort criteria entered by the user's front end, such as:

@sql + = ' ORDER by ' + @sortcol

This can be used to satisfy multiple column sorting. such as ' SalesOrderID, Ordertime Desc '. Although it is sufficient to satisfy the functionality, the client does not know the query itself, which may result in an error due to an incoming parameter that does not belong to the related table or other factors, especially the order by in the logical processing of T-SQL is near the last part, the SELECT statement may rename the original column, Operation, which causes the front end to not know the final column name of the SELECT. In addition, even the use of the correct name, but in the following may be due to table structure changes, column name changes and other factors brought error. This situation is very difficult to avoid, not too much to consider the problem may not be so serious, such as can be preprocessed in the following way:

SELECT @sql + = ' ORDER by ' + Case                @sortcol when ' OrderID ' and '      o.orderid ' when                             ' Emplyoeeid ' and then '   O.empl Oyeeid ' when                             ' ProductID ' then    ' od. ProductID ' when ' CustomerName ' and ' c.companyname ' when                             ' ProductName ' then  ' p.productname '                             ELSE ' O. OrderID '               end + case @isdesc if 0 Then ' ASC ' ELSE ' DESC ' end

Alternate table:

In the previous section of the alternate table, the case of different parameters accessing different tables was mentioned, which is not difficult to implement in dynamic SQL and can be rewritten from the from section to:

ROM dbo. ' + case @ishistoric if                  0 Then ' Orders ' while                  1 Then ' historicorders '             END + ' ojoin dbo ' + Case @ish Istoric when                  0 Then ' [Order Details] ' when                  1 Then ' historicorderdetails '             END + ' OD

However, in order to avoid the risk of SQL injection, it is not recommended to pass in the table name through the front-end program, but instead pass in some identity parameters and make table name selections inside the stored procedure.

Caching issues:

One of the advantages of parameterized dynamic SQL is that you can reduce the number of compilations by scheduling reuse. But caching is not always good, as mentioned in the Basic Skills section of the previous chapter:

1. exec [email protected]= ' 20050701 ', @todate = ' 20050701 '  2. exec [email protected]= ' 20050101 ', @todate = ' 20051231 '  

Although the parameter set is the same, when the values are different, if the data distribution of these different value is severely uneven, the execution plan cannot efficiently support all queries. This is common in both dynamic SQL and static SQL, so here's how to do this:

OPTION (RECOMPILE):

Yes, you saw it again. In the specific case mentioned above, if the query condition is @fromdate and @todate, add option (RECOMPILE):

IF (@fromdate is not null OR @todate are NOT null)   SELECT @sql + = ' OPTION (RECOMPILE) ' + @nl

Typically, when you find that there is a proper index on the query condition and the selection is very dependent on the input of the actual value, you can add option (RECOMPILE) so that you will always compile the best execution plan for the current statistic. But obviously this way will add some unnecessary compilation, such as the value of two executions is exactly the same, will still compile.

Index hints and other hints:

Sometimes you can try to use "hint, hints," which can be used to determine what parameters need to be passed in case, and to specify additional indexes that need to go on these parameters. But as mentioned earlier, tips should be used sparingly, especially index hints, unless you ensure that the index name never changes:

From   dbo. Orders o ' + case when @custid are not null and                                  (@fromdate are not null OR                                  @todate are not null) then                              ' with (INDE X = CustomerID) '                             ELSE '                         END

Another hint is to use optimize for. If you want the execution plan to always compile with the most occupied cases, such as C in the status type mentioned earlier, you can add:

IF @status is not NULL   @sql + = ' OPTION (OPTIMIZE for (@status = ' C ')) '

If you do not want the optimizer to generate an execution plan by sniffing parameters, you can use:

IF @fromdate is not null and @todate are NOT null   @sql + = ' OPTION (OPTIMIZE for (@fromdate UNKNOWN, @todate UNKNOWN)) '

The optimizer does not use the standard hypothesis, that is, around 10% to compile the query.

Summarize:

Dynamic SQL is powerful, but if the reader's ability to generalize is stronger, it can be seen that the problem with dynamic SQL is mainly in the inability to make good use of the plan cache or using an inappropriate execution plan, leading to performance issues.

There are many ways to use this type of situation, and if you can, consider other technologies that are not at the database level. But our goal is still one: to ensure that the execution plan for any parameter, at least most of the parameters are the best, and can be reused as much as possible.

Finally, it should be recalled that any technology or skill should be adequately tested in a test environment that is as close to the actual environment as possible to get the results you want.

T-SQL Dynamic Query (4)--Dynamic SQL