September 18, 2015 at 4:59 am
I talked to some people the other day that used pretty much all dynamic SQL.
I personally have not dived into dynamic SQL into anything that I do currently. But, with their heavy usage of it, I wondered why it was being used so much for their applications versus those who do not.
Can anyone share some reasons and possibly any of the pros and cons of doing so?
From what I know, it looks to be very complex, hard to manage and hard to hire for.
September 18, 2015 at 6:38 am
I use dynamic SQL to accomplish things I can't do with normal SQL. There are times, for example, when you want to select data from one of several tables, but you don't know which one until run time. Another example is when you want to read from one of several databases, but you don't know which one until run time. You can also use it for string replacements. There's nothing magical about it...it's just another tool in the toolbox that you can haul out when you need it.
The biggest thing to watch for is SQL injection. There are others, but I consider this one to be the biggest. You have to take precautions to make sure your data is safe.
September 18, 2015 at 7:41 am
This remind me of the old saying.
"If Your Only Tool Is a Hammer Then Every Problem Looks Like a Nail".
Always use the right tool for the job and things will work out a lot better. As previously mentioned there are some things I just couldn't do without using it but I wouldn't use Dynamic SQL when it isn't necessary.
September 18, 2015 at 7:55 am
If they use dynamic SQL for most of their SQL then I suspect they're either stuck in a rut or don't know the correct way to do things.
Someone needs to get them to put the hammer down and try out this new tool called a screwdriver. 🙂
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 18, 2015 at 9:49 am
Alvin Ramard (9/18/2015)
If they use dynamic SQL for most of their SQL then I suspect they're either stuck in a rut or don't know the correct way to do things.Someone needs to get them to put the hammer down and try out this new tool called a screwdriver. 🙂
That's what I was thinking.
I understand the concepts for when to use it here and there, but for everything? I mean, hrrm. Kind of scares me that the application or the people creating the application simply don't know what they want.
September 18, 2015 at 9:55 am
That's what I was thinking.
I understand the concepts for when to use it here and there, but for everything? I mean, hrrm. Kind of scares me that the application or the people creating the application simply don't know what they want.
Don't be surprised by this. Far too many things become the "standard" at many companies because someone "Googled" an answer.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 18, 2015 at 10:56 am
yb751 (9/18/2015)
This remind me of the old saying."If Your Only Tool Is a Hammer Then Every Problem Looks Like a Nail".
Always use the right tool for the job and things will work out a lot better. As previously mentioned there are some things I just couldn't do without using it but I wouldn't use Dynamic SQL when it isn't necessary.
I agree. I have seen where dynamic SQL is used and it was easy to replace the dynamic SQL with straight SQL code. But there are also times (quite a few where I currently work) where it is the necessary tool.
September 19, 2015 at 4:32 am
I think the biggest problem (once SQL Injection is protected against) with dynamic SQL is if the queries are not parametrised.
Running
SELECT Col1, Col2, ... FROM ORDERS where CustomerID=123
SELECT Col1, Col2, ... FROM ORDERS where CustomerID=456
is two entries in the query plan cache.
SELECT Col1, Col2, ... FROM ORDERS where CustomerID=@Parameter
run with @Parameter=123 and then with @Parameter=456 is only one entry in the query plan, more importantly the plan is reused for the second one which saves all the time of working out what the query plan needs to be, as well as reducing the cache memory used / thrashed.
But ... if CustomerID=123 only has one order and CustomerID=456 has thousands of orders it could well be that the Query Plan that gets in the cache, from the first query, is useless for the second one ...
Another consideration is permissions. A SProc requires EXECUTE PERMISSION to run it. You can grant that to a user who has no direct access to the tables/views that it uses, because you create the Procedure with suitable, elevated, permissions.
With dynamic SQL the user has to have permission on the tables (you can work around that, eg EXECUTE AS / CERTIFICATES, but given the "slant" of the conversation I'm assuming that companies that are "throwing" dynamic SQL at the server are not doing that 😉 ). If the users figure out how to set up an ODBC connection to the database they can gain access with Excel :w00t:
I did some work for a client where the DEVs were firing dynamic SQL at their server and had some performance problems. I explained to them about using sp_ExecuteSQL to issue parametrised queries fully expecting them to say that it would be months of work to change the code ... but ... they said that all their database queries went through a single function, in their APP code, and it would be trivial to change it from concatenating all the strings into one SQL statement to use sp_ExecuteSQL and @Parameters. In an afternoon that had that made the change and it revolutionised the performance of their APP, which was a nice outcome. 😎
September 19, 2015 at 11:53 am
Kristen-173977 (9/19/2015)
I think the biggest problem (once SQL Injection is protected against) with dynamic SQL is if the queries are not parametrised.
Dynamic SQL can be parameterised, and protecting against SQL Injection is done, in most cases, by ensuring that it is parameterised, except for the rare scenario where the user input can be white-listed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2015 at 2:17 am
GilaMonster (9/19/2015)
protecting against SQL Injection is done, in most cases, by ensuring that it is parameterised, except for the rare scenario where the user input can be white-listed.
We parametrise for performance, and take the secondary (for us 🙂 ) benefit of protecting against SQL injection, but where we cannot parametrise dynamic SQL (table or column name substitution, for example) we just REPLACE any single-quote with a pair of single quotes.
September 21, 2015 at 8:50 am
Kristen-173977 (9/20/2015)
GilaMonster (9/19/2015)
protecting against SQL Injection is done, in most cases, by ensuring that it is parameterised, except for the rare scenario where the user input can be white-listed.We parametrise for performance, and take the secondary (for us 🙂 ) benefit of protecting against SQL injection, but where we cannot parametrise dynamic SQL (table or column name substitution, for example) we just REPLACE any single-quote with a pair of single quotes.
Kristen,
Just confirming what I'm reading here... Do you actually have a production application with dynamic table / column names in the SQL?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 21, 2015 at 9:10 am
WayneS (9/21/2015)
Kristen-173977 (9/20/2015)
GilaMonster (9/19/2015)
protecting against SQL Injection is done, in most cases, by ensuring that it is parameterised, except for the rare scenario where the user input can be white-listed.We parametrise for performance, and take the secondary (for us 🙂 ) benefit of protecting against SQL injection, but where we cannot parametrise dynamic SQL (table or column name substitution, for example) we just REPLACE any single-quote with a pair of single quotes.
Kristen,
Just confirming what I'm reading here... Do you actually have a production application with dynamic table / column names in the SQL?
I'm not sure about Kristen's case, but I had to deal with dynamic table names before. They created a weekly snapshot for a table appending the year and week to the name, so dynamic sql was needed to query historic data. I know that this could have been prevented with an additional date column in the table, but it was too late for this when I arrived and a code change for this wasn't a priority.
For this, I remember to use something like: QUOTENAME( 'MyTableName' + @YearAsChar4 + @WeekAsChar2) to prevent injection (even if no user input was allowed and strings where too short to be a real risk).
September 21, 2015 at 10:57 am
WayneS (9/21/2015)
Just confirming what I'm reading here... Do you actually have a production application with dynamic table / column names in the SQL?
Only one I can think of (i.e. a proper column-name-substitution-in-Dynamic-SQL and in Production) is the ORDER BY clause. (We do have caveats on this 🙂 )
We have plenty of stuff to assist DEVs that messes about with Tables and Columns in dynamic SQL, but that is just for mechancially-generating CRUD and stuff like that.
Our Index Rebuild stuff uses dynamic SQL and will be substituting Table and Index names and so on (but I suspect that would be considered to be in a different "pocket" to "Production")
September 21, 2015 at 1:22 pm
I think it depends a lot on what the SQL is being used for...
Personally I write a lot of report procs that can have a several optional/multi-valued parameters. For those, dynamic sql is my weapon of choice.
Yes, using the OPTION(RECOMPILE) is good for handling optional parameters and a splitter function will take care of the multi-valued arrays that SSRS supplies, but, at a certain point it just makes sense to use dynamic sql... Especially when you have a large number of tables joined for the sole purpose providing a filter column.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply