ad hoc queries

Question

ad hoc queries

mah_j

SSCrazy

Points: 2931
More actions
May 15, 2012 at 3:50 am

#268869

Hi
What is ad hoc queries?
and what is the difference between ad hoc queries and dynamic queries?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

vinu512 SSCoach Points: 15729 More actions · Answer 1

Different people may have different point of Views on this. But, from what I know:

An AD-HOC Query is a query which is created on the fly, for a specific Non-Generalizable one time requirement and may not be saved(as a procedure or function or a script) for using again. AD-HOC Query can be put together in a lot of ways like: writing a query in Query Editor(SSMS/GUI), generating dynamically etc.

AD-HOC Query is a broader concept compared to Dynamic Query. AD-HOC queries may be Static or Dynamic but are only created for a specific Non-Generalizable one time requirement

Dynamic queries are normally used when the query depends on a Dynamic context. They are Dynamically generated depending on various Elements and Attributes which are Dynamic in nature and are prone to change.

Dynamic Queries are a type of AD-HOC querying. The Query Optimizer also categorizes Dynamic Queries as AD-HOC.

Hope this helped.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

kwe477 SSC Eights! Points: 820 More actions · Answer 2

Excerpt from below link:

Ad-hoc query:

created and executed manually through a client. e.g., a user opens up SSMS, writes a query and executes it.

Dynamic queries:

are nothing more than ad-hoc queries built using a dynamic string. Dynamic queries are considered ad-hoc queries by the optimizer because they are not prepared SQL statements like SPs. The fact that you can parameterize dynamic queries isn't so much the differentiating factor. The distinction here is dynamic queries are issued from an application whereas ad-hoc queries are typed into a client, like SSMS.

See here:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d001c105-bfde-431d-93aa-9ee2b2775ebc

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

kwe477 (5/15/2012)
Excerpt from below link:
...
Dynamic queries:
are nothing more than ad-hoc queries built using a dynamic string. Dynamic queries are considered ad-hoc queries by the optimizer because they are not prepared SQL statements like SPs. The fact that you can parameterize dynamic queries isn't so much the differentiating factor.
...

What about if my application will generate dynamic sql query, then prepare it (Command.Prepared), and only then execute it?

Actually, SQL caches query plans for all type of queries, isn't it?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

kwe477 (5/15/2012)
Excerpt from below link:
Ad-hoc query:
created and executed manually through a client. e.g., a user opens up SSMS, writes a query and executes it.
Dynamic queries:
are nothing more than ad-hoc queries built using a dynamic string. Dynamic queries are considered ad-hoc queries by the optimizer because they are not prepared SQL statements like SPs. The fact that you can parameterize dynamic queries isn't so much the differentiating factor. The distinction here is dynamic queries are issued from an application whereas ad-hoc queries are typed into a client, like SSMS.
See here:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d001c105-bfde-431d-93aa-9ee2b2775ebc

Like anything else, it depends. Poorly written dynamic queries will do pretty much as you say. Correctly written dynamic queries are actually prepared and cache a repeatable execution plan that can, in fact, be reused just like the plan for an SP. That's what sp_ExecuteSQL is all about. Read about it in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kwe477 SSC Eights! Points: 820 More actions · Answer 5

Eugene Elutin (5/15/2012)
kwe477 (5/15/2012)
Excerpt from below link:
...
Dynamic queries:
are nothing more than ad-hoc queries built using a dynamic string. Dynamic queries are considered ad-hoc queries by the optimizer because they are not prepared SQL statements like SPs. The fact that you can parameterize dynamic queries isn't so much the differentiating factor.
...
What about if my application will generate dynamic sql query, then prepare it (Command.Prepared), and only then execute it?
Actually, SQL caches query plans for all type of queries, isn't it?

Another excerpt from a great white paper:

http://msdn.microsoft.com/en-us/library/ee343986(v=SQL.100).aspx

Prepared queries. This method — which is similar to the sp_executesql method— also promotes query plan reuse. The batch text is sent once at the "prepare" time. SQL Server responds by returning a handle that can be used to invoke the batch at execute time. At execute time, a handle and the parameter values are sent to the server. ODBC and OLE DB expose this functionality via SQLPrepare/SQLExecute and ICommandPrepare

Yes, it caches query plans for all types but there are exceptions.

See the paragraph in the white paper called:

Factors that affect plan-reuse

Jared SSC Guru Points: 61793 More actions · Answer 6

Its kind of like asking what's the difference between a salad and a fruit. A salad can be made of fruit or vegetables, and a fruit can be in a salad or a part of other things. They are really not comparable in the sense that they are opposites or that you use one or the other.

An ad hoc query is kind of as mentioned earlier, but it does not have to be executed "manually" or through a client. It also does not have to be for one-time use. I will try to explain the best I can, so lease chime in people if this needs some help:

An ad hoc query is a query executed as the basic DML language that may or may not have a saved execution plan. It may or may not be dynamic. It is not a stored procedure. It may be executed from a GUI, command line, application, or many other means. Maybe even more simply, the DML itself is not stored as or within an object of the database?

Examples: Line of code in C# that connects to the database and SELECTs from a table.

Running any DML in SSMS.

Example of something not ad hoc: Executing a stored procedure from a line of code in an application.

Executing a stored procedure from SSMS

Dynamic queries are simply queries that can change based on parameters passed to them or based on different conditions. I "believe" that they can be ad hoc or not.

This is not meant to be published fact, but is my best understanding thus far.

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

SQLKnowItAll (5/15/2012)
Its kind of like asking what's the difference between a salad and a fruit. A salad can be made of fruit or vegetables, and a fruit can be in a salad or a part of other things. They are really not comparable in the sense that they are opposites or that you use one or the other.

Heh... and to continue the methaphor...

"Knowledge is knowing that a Tomato is a fruit. Wisdom is knowing that you shouldn't use it in a fruit salad." 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mah_j SSCrazy Points: 2931 More actions · Answer 8

Thanks all for replies

You mean ad hoc query is a batch that can be executed for example from an application

or by sp_executesql ...?!!!!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

mah_j (5/15/2012)
Thanks all for replies
You mean ad hoc query is a batch that can be executed for example from an application
or by sp_executesql ...?!!!!

No.

I'm not sure how this went in that direction but there are two types of "ad hoc" queries.

The informal definition of an ad hoc query is nothing more than a "one off" query that someone may write just to do a quick lookup or whatever. It can be done using any tool but most people do it from SSMS because that's one of the easiest to quickly write a query for a quck lookup, etc.

The more formal definition is when you run OPENROWSET or OPENDATASOURCE. Both of those require that the "ad hoc distributed queries Option" of SQL Server be enabled.

Dynamic queries typically mean "Dynamic SQL" where pieces of calculated SQL are placed in a variable and then the contents of the variable are executed. Dynamic SQL may or may not have "ad hoc" queries that use OPENROWSET or OPENDATASOURCE in them or not and they may be a "one off" or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

essbasecube 59146 Grasshopper Points: 16 More actions · Answer 10

vinu512 - Tuesday, May 15, 2012 6:25 AM
Different people may have different point of Views on this. But, from what I know:An AD-HOC Query is a query which is created on the fly, for a specific Non-Generalizable one time requirement and may not be saved(as a procedure or function or a script) for using again. AD-HOC Query can be put together in a lot of ways like: writing a query in Query Editor(SSMS/GUI), generating dynamically etc.AD-HOC Query is a broader concept compared to Dynamic Query. AD-HOC queries may be Static or Dynamic but are only created for a specific Non-Generalizable one time requirementDynamic queries are normally used when the query depends on a Dynamic context. They are Dynamically generated depending on various Elements and Attributes which are Dynamic in nature and are prone to change.Dynamic Queries are a type of AD-HOC querying. The Query Optimizer also categorizes Dynamic Queries as AD-HOC.Hope this helped.

THANK YOU! understanding differences between AD-HOC vs Dynamic vs Parameter vs Hard Coded Queries makes sense now!