Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmilam
Posting Yak Master
185 Posts |
 Posted - 2010-09-01 : 17:56:45
|
| What is the performance penalty of using dynamic SQL? What factors affect this performance? Any links (or brief discussion) appreciated. Would like to make the case to my boss that dynamic SQL in general is to be avoided, unless some special case is needed, such as using a table name as a variable. The queries are purely internal and are not used to support external-facing forms, etc. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2010-09-01 : 19:02:50
|
| Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-02 : 06:46:21
|
"Would like to make the case to my boss that dynamic SQL in general is to be avoided"If you need very generic WHERE clauses - e.g. where the user can type in one/many of Name, Address, Phone number, Age, Gender ... - then I think that Dynamic SQL would be preferable. It is important that you use sp_ExecuteSQL to run the SQL, and that you have carefully considered SQL Injection (even if no threat there is still the issue that a user trying to include ' in their search string could break the dynamic SQL). Also, the issue of permissions - but that is solved in latest versions of SQL Server using Certificates.I would NOT use Dynamic SQL where the table name is the substituted part - I would have separate Stored Procedures for each table - even if "identical" - they have a nasty habit of "I just need this special validation / data mangling on this table". For Stored Procedures for CRUD we mechanically generate them initially and then hand-code changes where needed. I would guess that 80% of ours are as-generated and without any change (and could thus use dynamic SQL if we chose to go down that route - but we haven't done that  ) |
|
|
|
|
|
|
Dynamic SQL performance