Recently I had mess up with dynamic SQL , So whats next !!! , I started to explore on this topic and post it here , when I googled to gather some information , but what I found was , this topic has been already drilled to the core , carved and with the sculpture mounted on the walls of SQL Server by some of the great SQL Server guru's , much before an year I started to work with SQL Server .
For my regular readers , I will definitely share the good links with the abstract from the same .
To execute a string , we can make use of sp_executesql or EXEC - “Dynamic String Execution” (DSE)
As mentioned in the BOL :
To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server .
sp_executesql - Allows for statements to be parameterized , It’s more secure than EXEC in terms of SQL injection and also like Stored procedure , sp_executesql reuses cached query plans on successive execution , it will be a performance problem in scenario's where parameter value changes
on successive execution and results in big variation in the selectivity of rows to be returned with same cached plan .
As highlighted in the title of this post , security and performance are the key factors to be considered when using dynamic SQL .
****^#( Hope this post was useful !!@:)$(!!
For my regular readers , I will definitely share the good links with the abstract from the same .
To execute a string , we can make use of sp_executesql or EXEC - “Dynamic String Execution” (DSE)
As mentioned in the BOL :
To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server .
sp_executesql - Allows for statements to be parameterized , It’s more secure than EXEC in terms of SQL injection and also like Stored procedure , sp_executesql reuses cached query plans on successive execution , it will be a performance problem in scenario's where parameter value changes
on successive execution and results in big variation in the selectivity of rows to be returned with same cached plan .
- sp_executesql reduce the risk of SQL injection than EXEC
- cached plan problem with sp_executesql
- Again , EXEC and sp_executesql – how are they different?
- OPTION (RECOMPILE) for cached plan problem with sp_executesql
- How to avoid SQL Injection when using EXEC
- Complete picture - Dynamic Search Conditions in T‑SQL Version for SQL 2008 (SP1 CU5 and later)
Related useful topics :
- Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
- Execution Plan Caching and Reuse
- Parameter Sniffing Problem and Possible Workarounds
- Statement execution and why you should use stored procedures
- T-SQL Script to check the syntax of dynamic SQL before execution
- How to build and execute dynamic SQL queries?
- Dynamic SQL & Stored Procedure Usage in T-SQL
As highlighted in the title of this post , security and performance are the key factors to be considered when using dynamic SQL .
****^#( Hope this post was useful !!@:)$(!!
No comments:
Post a Comment