SQL Server Query Store - What is considered an 'ad-hoc' query?

After a little bit of searching, I wasn't able to find a concrete and satisfying source from Microsoft's documentation to answer this question. There are many good third-party descriptions and definitions of adhoc/ad-hoc/ad hoc out there, but for the specificity of this question I think one close to the source is ideal.

Moving past generic (but still accurate) definitions like this SO post (thanks SqlWorldWide), if we look at what the documentation does say on the topic, it agrees with what you've mentioned about the definition being based off of the number of executions, I think to the point that we can take it as fact.

The article on optimizing for ad hoc workflows says,

When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused. The compiled plan stub allows the Database Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Database Engine compiles the batch ... and adds the full compiled plan to the plan cache.

So it would appear the the ad hoc query server configuration option also uses the definition of a single execution as the definition of ad hoc. If the query continues to get executed and generates the same plan it would no longer be treated as such.

The article on Best Practices for Query Store also aligns with this,

Compare the number of distinct query_hash values with the total number of entries in sys.query_store_query. If the ratio is close to 1 your ad-hoc workload generates different queries.

This, of course, is for queries that are not already used as stored procedures, parameterized, etc. because they can be recognized and treated appropriately right off the bat.

So based on all this we can say that a query is treated as ad hoc if:

• It is not parameterized
• It is not programmatically stored in the database (stored proc, function, trigger, etc.)
• The same query is only executed one time OR The same query is executed multiple times but generates a different query plan for each subsequent execution.

For ad-hoc queries, the object_id column in the sys.query_store_query DMV will be 0, as stated in the sys.query_store_query documentation:

object_id:

ID of the database object that the query is part of (stored procedure, trigger, CLR UDF/UDAgg, etc.). 0 if the query is not executed as part of a database object (ad-hoc query).

You can identify ad-hoc queries based on that value, even though it is not explicitly stated as "this is the definition of ad-hoc queries". :)