December 31, 2007 at 5:01 am
Experts,
I have written a dynamic query recently.
The Query is to generate the column at runtime.
Say for example,
Table Name:CC
Columnname
Eno
Ename
Salary
Then my query will generate the sql select query as
select Eno,Ename,Salary from Emp
Sometime use may enter more columns in the CC table like
Table Name:CC
Columnname
Eno
Ename
Salary
DOB
then my query will generate
select Eno,Ename,Slary,DOB from Emp
So my questions are
1) Will my logic is correct? i.e i have choosen dynamic query to accomplish this task.
2) is dynamic query is the only option to solve it ?
3) if not is there any way to solve it ?
4) if so, will dynamic query give good performance ?
5) Basically , i want to know whether using dynamic query is good or bad or it depends.
karthik
December 31, 2007 at 5:38 am
Dynamic queries good/bad : it depends.
Usually they give the same performance than stored procedures but lack some encapsulation. (since sql server will try to cache the plan)
For this goal, dynamic sql is sound. (in my opinion)
Beware of sql-injection attacks. If the dynamic query generation is a bit sloppy one might sneak an attack in.
December 31, 2007 at 10:52 am
what you are in effect providing is the ability to write total ad-hoc queries against a table, these types of queries are almost impossible to tune ( with efficient indexing ) and once tables get to a reasonable size will cause table scans. poor performance and eventually locking and blocking.
plans may or may not be cached and/or reused - but if they table scan it doesn't really make much difference.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply