202

I'm reading a book about SQL. In that book there's the term Ad Hoc Query, which I don't understand.

What exactly is an ad hoc query?

Ben
  • 51,770
  • 36
  • 127
  • 149
zihado
  • 2,311
  • 3
  • 15
  • 8

11 Answers11

258

Ad hoc is latin for "for this purpose". You might call it an "on the fly" query, or a "just so" query. It's the kind of SQL query you just loosely type out where you need it

var newSqlQuery = "SELECT * FROM table WHERE id = " + myId;

...which is an entirely different query each time that line of code is executed, depending on the value of myId. The opposite of an ad hoc query is a predefined query such as a Stored Procedure, where you have created a single query for the entire generalized purpose of selecting from that table (say), and pass the ID as a variable.

David Hedlund
  • 128,221
  • 31
  • 203
  • 222
  • 20
    @David Hedlund your query is vulnerable to `SQL injection attacks`...You must use parameter. – Paridokht Oct 22 '13 at 08:23
  • 60
    @Paridokht: Certainly. I'm not advocating that use, I'm explaining the concept of an ad hoc query, which was the question. – David Hedlund Oct 22 '13 at 10:30
  • 13
    @DavidHedlund: Yes,I Understand..but may be it's better to mention it in your answer because there are users who read this and do not have any idea of this attack. – Paridokht Oct 22 '13 at 10:57
  • 62
    If a user reads it and does not have idea about SQL injection, they are not working on anything which could be of hacker's interest. Let's not go to extremes... – Michal B. Jan 09 '14 at 09:44
  • 15
    As a developer with years of experience now, I've been learning a lot of things while I was looking for something completely different than what I look for. So IMHO adding a sentence just like '***... don't forget to do necessary controls and sanitization for myId here***' wouldn't cause any harm. – scaryguy Dec 11 '15 at 03:15
  • 5
    Although this is probably the simplest type of ad hoc query, this answer does not adequately highlight the difference between an "ad hoc" and a "predefined" query. In the explanation, `id` is a dynamic value in both the predefined stored procedure and the ad hoc query. This may be confusing for beginner readers. – light Mar 25 '16 at 10:11
  • 1
    @light, I'm indeed confused. So using this query, what would differentiate it as an ad-hoc query vs a stored procedure? Is it only the need to use CREATE PROCEDURE and define the parameters? – OfirD Jan 06 '17 at 12:37
  • 4
    @HeyJude yes. A stored procedure is typically created using syntax like CREATE PROCEDURE. It is stored in the DBMS and the DBMS can optimize the execution of the query. Ad hoc queries, in contrast, are not stored on the DBMS, and the engine must go through all the parsing, query plan... etc. in order to execute the query. – light Jan 07 '17 at 11:35
32

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools.

Check: http://www.learn.geekinterview.com/data-warehouse/dw-basics/what-is-an-ad-hoc-query.html

haltunbay
  • 615
  • 6
  • 15
4

An Ad-hoc query is one created to provide a specific recordset from any or multiple merged tables available on the DB server. These queries usually serve a single-use purpose, and may not be necessary to incorporate into any stored procedure to run again in the future.

Ad-hoc scenario: You receive a request for a specific subset of data with a unique set of variables. If there is no pre-written query that can provide the necessary results, you must write an Ad-hoc query to generate the recordset results.

Beyond a single use Ad-hoc query are stored procedures; i.e. queries which are stored within the DB interface tool. These stored procedures can then be executed in sequence within a module or macro to accomplish a predefined task either on demand, on a schedule, or triggered by another event.

Stored Procedure scenario: Every month you need to generate a report from the same set of tables and with the same variables (these variables may be specific predefined values, computed values such as “end of current month”, or a user’s input values). You would created the procedure as an ad-hoc query the first time. After testing the results to ensure accuracy, you may chose to deploy this query. You would then store the query or series of queries in a module or macro to run again as needed.

Eliot G York
  • 151
  • 6
1

An Ad-Hoc Query is a query that cannot be determined prior to the moment the query is issued. It is created in order to get information when need arises and it consists of dynamically constructed SQL which is usually constructed by desktop-resident query tools. An ad hoc query does not reside in the computer or the database manager but is dynamically created depending on the needs of the data user.

In SQL, an ad hoc query is a loosely typed command/query whose value depends upon some variable. Each time the command is executed, the result is different, depending on the value of the variable. It cannot be predetermined and usually comes under dynamic programming SQL query. An ad hoc query is short lived and is created at runtime.

Ketan Anand
  • 11
  • 2
0

Ad hoc queries are those that are not already defined that are not needed on a regular basis, so they're not included in the typical set of reports or queries

user3134258
  • 1
  • Can you explain what you're writing more? What is the "typical set"; do you mean compiled code? – Ben Dec 25 '13 at 09:58
0

Ad-hoc Statments are just T-SQL Statements that it has a Where Clause , and that Where clause can actualy have a literal like :

Select * from member where member_no=285;

or a variable :

declare @mno INT=285;
Select * from member where member_no=@mno
A.HADDAD
  • 1,809
  • 4
  • 26
  • 51
0

Ad-hoc Query -

  • this type of query is designed for a "particular purpose,“ which is in contrast to a predefined query, which has the same output value on every execution.
  • An ad hoc query command executed in each time, but the result is different, depending on the value of the variable.
  • It cannot be predetermined and usually comes under dynamic programming SQL query.
  • An ad hoc query is short lived and is created at runtime.
Rohan Devaki
  • 2,931
  • 1
  • 14
  • 22
-1

Also want to add that ad hoc query is vulnerable to SQL injection attacks. We should try to avoid using it and use parameterized SQLs instead (like PreparedStatement in Java).

xli
  • 2,420
  • 2
  • 20
  • 27
  • 8
    An ad-hoc query is _not_ vulnerable to SQL Injection. An unparametized query that accepts user-input is vulnerable to SQL Injection. – Ben Dec 25 '13 at 10:00
  • @Ben An ad-hoc query is always unparameterized. It cannot be SQL injection attacked if we don't expose the variables to users; but the point is ad-hoc query is risky to SQL Injection. – xli Dec 31 '13 at 15:06
-4

An Ad-Hoc query is:

  1. Pre-planned question.
  2. Pre-scheduled question.
  3. spur of the moment question.
  4. Question that will not return any results.
Paresh Mayani
  • 127,700
  • 71
  • 241
  • 295
Ravi
  • 9
  • 1
  • 1
    What does this mean in terms SQL? Also, not all "Questions that will not return any results." are ad hocs. – jumxozizi Jan 18 '17 at 11:44
-6

Ad hoc query is type of computer definition. Which means this query is specially design to obtain any information when it is only needed. Predefined. refer this https://www.youtube.com/watch?v=0c8JEKmVXhU

Sanyog Tiwari
  • 33
  • 6
-7

In Sql Server "Ad Hoc Query" is also used for Ad Hoc Distributed Queries. These are ad hoc queries on an other server via OpenRowset or OpenDatasource. Ad Hoc Distributed Queries are only allowed after configuring so. This is a Server configuration option.

Expansion
  • 7
  • 1
  • this is not about sql Server. the question was not about a particular entreprise solution but rather about a general understanding of the meaning ad hoc queries – arthur May 30 '13 at 13:46