Dynamic SQL Writing Efficient Queries on the Fly

Dynamic SQL Writing Efficient Queries on the Fly ED POLLACK DATABASE ADMINISTRATOR COMMERCE HUB

Coming Soon… • Dynamic SQL: Applications, Performance, and Security: • http: //www. amazon. com/Dynamic-SQL-Applications-Performance. Security/dp/1484218108

What is Dynamic SQL? • Build up a TSQL statement as a character string. • Can incorporate unknowns into the SQL statement: variables, parameters, and table data. • Statements can be simple and executed all at once, or built up over the course of a stored proc, in order to handle more complex logic. • String manipulation functions can be used to facilitate creating the command string. • Quick SQL Example: Dynamic SQL basics.

Advantages of Dynamic SQL • Optional or custom searches. • Dynamic WHERE, GROUP BY, HAVING, TOP X, ORDER BY, windowing functions, etc… • Can greatly speed up complex queries where, at any given time, only a small amount of the SQL is needed. • Generate large or complex SQL statements quickly and/or automatically. • Execute TSQL on other databases or servers.

Dynamic SQL’s Weaknesses • Character strings with apostrophes MUST always be managed correctly (SQL injection) • Dynamic SQL can become very complex, difficult to read, hard to maintain and to debug. • Permissions are different than with standard SQL. • Unexpected results from unexpected input. • Dynamic SQL (within quotes) always compiles successfully, but could error on execution. • Cannot use dynamic SQL in functions.

Basic Tips for Writing Better Dynamic SQL • Document thoroughly!!! • Debugging: Use PRINT statements liberally to preview SQL text • Test thoroughly all use cases, especially “dumb” input • For complex procs, consider a @debug parameter • • Write dynamic SQL text just like you would regular SQL, with similar spacing and indenting. • Always check spacing! • NVARCHAR vs. VARCHAR (use the right one!) • SQL Example: Good dynamic SQL Style

Scope • Each dynamic SQL statement occurs in its own local scope! • Variables & objects declared in your dynamic SQL statement will not be available elsewhere in your stored proc, or in other dynamic SQL statements. • # temp tables will be unavailable outside of a dynamic SQL statement. • ## temp tables can be created in dynamic SQL & used anywhere (beware security/dupes/misuse)

Efficiently Generating Lists • Dynamic SQL can be used to quickly build lists--either from variable inputs, or from columns of data in target tables. • SQL Example: Efficiently Generating Lists From Table Data

sp_executesql • System procedure that allows SQL text to be efficiently executed. • Must use NVARCHAR for command string. • Parameters can be passed in. • Output parameters can be specified so that data can be retrieved from the dynamic SQL. • Allows for execution plan reuse (if desired). • SQL Example: sp_executesql

Parameter Sniffing • Using sp_executesql will allow for plan reuse and force parameter sniffing. • Using EXECUTE/EXEC will cause the statement to be executed completely dynamically. • Queries are cached based on their EXACT text. A stored proc or sp_executesql allows their contents to be cached, with the parameters handled separately. • Parameter sniffing is generally a good feature. It is how SQL Server reuses execution plans. In some scenarios, we may want to change this, but this will typically be rare.

Parameter Sniffing: SQL Example

SQL Injection • SQL Example: SQL Injection • Converting quotes into double-quotes is a common solution, but not necessarily good enough! • Ensure security is limited enough to not allow purposeful (or accidental) access. This counts for user SQL accounts, but also for web logins or process accounts. • Use sp_executesql for all searches w/ user input. • Never expose error messages to the end user! • Use QUOTENAME() for database objects. • Use dbo (or schema name) with all object names.

SQL Injection (cont)

Permissions & Security • Dynamic SQL does not benefit from ownership chaining! • Ensure the user running dynamic SQL has the correct permissions. • EXEC(@SQL) AS USER = ‘Ed’ • EXEC(@SQL) AS LOGIN = ‘MYLAPTOPEd’ • Beware disk & OS permissions when using xp_cmdshell or any other OS level commands.

Saving Output • You can insert the results of a dynamic SQL select into a temp table or table variable. • This can be very useful in statements where the column list is known, but the contents can vary greatly. • Using the OUTPUT keyword on a parameter, you can output data from a dynamic SQL command directly to the parameter. • SQL Example: Saving Dynamic SQL Output

Bonus: The Crazy Dynamic Pivot • PIVOT can allow a row set to be flipped into column headers… • …But the column names must be predefined! • Dynamic SQL allows for an ad-hoc column structure when you want data to determine this, and not a static list. • SQL Example: The Crazy Dynamic Pivot

Applications and Ideas • SQL Server schema search. • SQL Server schema documentation. • Backups • Index Maintenance • …

Conclusion • • • Dynamic SQL is great, but only use when needed. Always cleanse inputs Always verify security & access to objects Be a neat freak & document thoroughly Be creative! Come to SQL Saturday Albany on July 30 th!!! • http: //www. sqlsaturday. com/513 • How to find me: • • • ed 7@alum. rpi. edu @Edward. Pollack SQL Server Central SQL Shack Facebook