Dynamic sql is much talked about here on the site (click

here to view a search on it), both in articles and in the discussion area. Robert

Marda has a great series up that talks about it. What I'd like to do here is

provide something that you can give to the new developer who has to make a

design choice.

What is dynamic sql? It's any sql query built up on the client and

submitted to the server, or it can be a query built up and executed

inside a stored procedure. A good example is to think of building a search form

where you can enter a half dozen different elements, you build the query at run

time based on which fields in the form were populated.

What makes a query dynamic? If you're supplying the table name at run

time, customizing the select list or the where clause, it's dynamic. If you're

using ADO and doing a connection.execute and passing a built up string, it's

dynamic. If you're building up a string variable inside a stored proc and

running it using either exec() or sp_executesql, it's dynamic. If you're just

building all your queries in your app so you don't have to go through your DBA,

they are all dynamic.

Why is it bad? Three reasons. First, you generally don't get the

benefits of a compiled query plan. I say generally because if you do it

carefully, you can get some plan reuse. The idea is it takes SQL some amount of

time (that varies based on the complexity) to figure out the best way to get the

data. It saves that plan so that when you re-run the query, you don't have to

figure out the plan all over again - saving you time and server expense. Second,

it provides a huge opportunity for a sql injection attack. Coding to protect

against injection attacks is more work, and easily forgotten. The final reason

is security. Dynamic sql requires that security be evaluated for every object

referenced, which means you usually have to grant access to the base tables,

something to be avoided if at all possible.

What is a static query? Pretty much just the opposite of dynamic. It's

a stored procedure, not code sent from the client app. SQL will compile and

preserve the query plan, making it faster to execute. If the owner of the

procedure is the owner of all the tables being used in the query, no further

security checks are done, making it faster (ownership chaining). No chance of

injection attacks.

How can I avoid it? As Robert points out in his series, you can do

some coding in a stored procedure that essentially provides a separate block of

code for each set of possibilities. You can also use like instead of equals in

the where clause. Sometimes this is a good idea, sometimes

not. It means more code to maintain and debug. I recommend using this when the

number of possibilities is small or you really, really need that extra bit of

performance, or you truly cannot grant access to the base tables.

How can I do it safely? A good rule to follow is never to grant access

to the base tables. My recommendation is to build a view for each table (and

consider if it really needs to all columns) and grant only select access on the

view. Use only the view when coding dynamic sql. You still take the performance

hit of the security check, but there is no possibility that the user can do an

injection attack containing a delete * from table. Hopefully you're running with

minimal permissions so that there is no possibility of the user dropping objects

or adding users to the server.

How much of a performance hit does it cause? Good question and one

that is hard to answer. Not a lot. It's probably only going to matter if you're

running the query a lot - lots of users submitting variations of the query

continuously. The performance hit is a combination of the time required to

resolve the security issue and the time to generate a query plan. Of the two,

both happen pretty fast but I suspect the query plan takes longer.

How can I minimize the performance hit? SQL gives you the option of

using exec() or sp_executesql. Of the two, sp_executesql is the better choice,

it will try to re-use previous plans that match the query exactly. You can boost

this some by using parameters in your sql, something I rarely see done.

SP_executesql also has the advantage of supporting output parameters so you can

return info from the dynamic session back into the calling stored procedure. ADO

automatically uses sp_executesql when you do a connection.execute.

Is it better to build the query on the client or in a stored procedure? You

use a stored procedure for all the reasons outlined in "Why is it

bad?" above, plus one more - loose coupling. Putting data access code in a

stored procedure allows you to change the behavior without recompiling the

application. So which is better depends on whether that loose coupling is of any

benefit to you. Another point to consider is how changes get applied. Once you

code a proc, all changes go to the DBA (right?!) who reviews and applies them to

the production box. If you put the code in the application, you can change it at

any time. I'm not normally in favor of circumventing the DBA, but this may be a

valid case. In my opinion once you decide to use dynamic sql, I think it should

be built on the client. The string handling is usually better and you can

usually build/debug/test faster and easier in application code.

Are there categories of tasks that usually require dynamic sql? Sure.

Searches as I mentioned earlier. Crosstabs where you don't know all the column

names at design time. Decision support type queries (really a type of search).

Pure ad-hoc queries like you would run in Query Analyzer (or Access, or another

other tool that supports querying a sql database).

Is it really bad? It depends on a lot of things. If you're

using zero stored procedures - which means 100% of your queries are dynamic -

you're working the server harder than it needs to. If the server is running at

10% of capacity, no, it doesn't matter. If the client response time is fine, it

doesn't matter. If you've coded defensively against sql injection attacks, then

no, it's not really bad. A server represents a fixed amount of resources.

The job of the DBA is to use those resources efficiently and to secure access to

the data. If you need to run dynamic sql to provide your users with a search

function, the DBA is not there to say no, you can never do dynamic. But the DBA

should be reviewing it to make sure it no injection attack is possible, and to

determine if there is a way to convert it to a static query.

Realistically you can't avoid dynamic sql. You can employ some tricks, but

often at the expense of time to build and maintainability later on. Still, the

tricks are worth knowing. Hopefully I've given you something to think about, and

maybe answered some questions you hadn't thought to ask yet. Got more questions?

Post in the attached discussion area and I'll do my best to answer them.