iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
An ad hoc query is a database query written to answer a specific, often immediate question rather than a predefined request that runs repeatedly. In data warehousing, it commonly means a query composed for immediate use and adjusted as someone explores the results. In SQL Server terminology, it can have a narrower technical meaning: a single query that is not part of a stored procedure and is not parameterized or prepared.
What an ad hoc query means
The defining feature is its purpose: someone needs an answer that a standard report or saved query does not already provide, so they compose a query for that particular need. Teradata’s Vantage documentation describes the data-warehousing use as a query composed for immediate performance, in contrast with a permanent query run repeatedly without alteration.
Ad hoc work is often exploratory. A user may run a query, inspect the output, then change its filters or calculations to investigate a follow-up question. The query is not necessarily temporary in a technical sense; the point is that it was created for a particular information need rather than as a predefined recurring task.
Example of an ad hoc query
Suppose an analyst needs a quick list of names from a people table and writes:
#1 Best Overall
SELECT LastName, FirstName FROM Person.Person;Redgate uses this as a simple example of a query written directly for a particular request. The statement retrieves selected columns from a table. More generally, queries can retrieve and filter rows, summarize or calculate values, and combine information; other query types can also change data. Microsoft’s introduction to queries explains the broader role of queries in working with data.
General usage versus SQL Server terminology
“Ad hoc query” can describe a work pattern or a narrower database classification. Those meanings overlap, but they are not interchangeable.
| Context | Meaning | What to keep in mind |
|---|---|---|
| General or data-warehouse usage | A query composed for immediate use to investigate a specific question, often refined interactively. | This describes why and how someone uses the query. |
| SQL Server usage in Redgate’s explanation | A single query that is not included in a stored procedure and is not parameterized or prepared. | A query typed for immediate use is not automatically classified as ad hoc under this narrower definition. |
| Reusable query or report | A saved or permanent query intended to run again. | A saved query, stored procedure, and SQL application are distinct objects, even though they can all support repeatable work. |
Microsoft notes that a saved select query in Access can be reused as the source for a form, report, or another query. Its guide to creating a simple select query shows how to build one. Saving a query does not make it the same thing as a stored procedure; the useful contrast is between work intended for reuse and work composed for a particular request.
Does ad hoc mean dynamic?
No. “Ad hoc” refers to a query’s purpose or, in SQL Server’s narrower terminology, its technical classification; it does not simply mean that the query is dynamic. Redgate cautions that a statement with a changing hard-coded value may be parameterized by SQL Server, so the fact that someone entered a query for immediate use does not settle how SQL Server classifies it. For that distinction, see Redgate’s explanation of ad hoc queries.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Are ad hoc queries bad for performance?
Not inherently. One-off queries are a normal part of database work. Performance concerns depend on the database, query, and workload—not on the label alone.
For SQL Server, Microsoft documents optimize for ad hoc workloads as an option that may reduce memory use when single-use ad hoc plans account for a significant share of memory on an OLTP server. This is a workload-dependent setting, not a general requirement for every database. Microsoft also describes forced parameterization as an option but warns that parameter sensitivity can cause performance problems. Review the SQL Server documentation on optimizing for ad hoc workloads before changing server configuration.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Free tools Windows power users keep installed
One-click scans. No signup required.
