What is ad hoc query?

What is ad hoc query?

This is only a note.

I have never known what ad hoc queries are. I read them again today.Sqlserver enterprise management platform,To understand what is "ad hoc queries"

The following is my personal summary:

What is ad hoc query?
AD-HOC: in the form of a separate SQL statement to execute the query is an ad hoc query, for example: in C #ProgramOr create a query window in SSMs.

Self-typed SQLCodeIs an ad hoc query.

Put SQL code into the stored procedureStored ProcedureOrFunctionOrTriggerThe query to be executed is not an ad hoc query. Ad hoc: the query is performed on the spot. Of course, it does not mean to write code on the spot.

-------------------------------------------Gorgeous split line---------------------------------------------------------

So does the ad hoc query have no execution plan cache?

In sqlserver enterprise-level management platform

Adhoc: Ad hoc query

Adhoc statements
A group of batch processing commands that contain select, insert, update, and delete. For such commands, only the front and back are completely consistent.
The statement contains uppercase and lowercase letters, spaces, and line breaks. Only two identical statements can the SQL statement reuse the execution plan. Therefore, this requirement is quite high.

 1   --  For example, run the following command to query the SYS. [syscacheobjects] view twice in a row.  2   DBCC  Freeproccache  3   Go  4   Select   *   From SYS. [ Syscacheobjects  ]  5   Go  6   Select   *   From   [  Sys  ] . [  Syscacheobjects  ]  7   Go 

The execution plans of the two adhoc nodes are displayed in the second result set. The difference between them is that one has a carriage return and the other has no carriage return.

However, if the statement is the same, the second statement may reuse the preceding execution plan. Therefore, the ADHOC statement may not necessarily be reused in the execution plan.

 1   --  For example, if you run the same sentence twice, you will find that there is only one execution plan, and he has been used twice.  2   --  (Field usecount = 2)  3   4   DBCC  Freeproccache  5   Go  6   Select    *   From SYS. [  Syscacheobjects  ]  7   Go  8   Select   *   From SYS. [  Syscacheobjects  ]  9   Go 

After sql2005, you can enable forced parameterization on a single database. That is to say, for most statements running in this database,

SQL statements are parameterized before running. If the application often uses the ADHOC method to call the same statement, force parameterization may be helpful.

 

----------------------------------------------Gorgeous split line------------------------------------------------------------------------

Will distributed queries reuse execution plans?

I tested it using the following code:

 1   --  During the first execution, the execution plan cache is cleared first. For the second execution, comment out DBCC freeproccache.  2   DBCC  Freeproccache  3   Go  4   5   6  Select   *   From   7   OpenRowSet ( '  Microsoft. Jet. oledb.4.0  ' , '  C: \ Users \ Administrator \ Desktop \ course. MDB  ' ; '  Admin  ' ; ''  , Course_baseinfo) 8   Go  9   10   Select   [  Cacheobjtype  ] , [  Objtype  ] , [  Dbid  ] , [  Usecounts  ] ,[  SQL  ]    From SYS. [  Syscacheobjects  ]  11   Where   [  Dbid  ]  =  1  12   Go 

According to the usecounts field, each execution increases progressively, indicating that an execution plan is reused.

Attach the MDB file for testing. Remember to install the Office when using distributed query. If it is a 64-bit operating system, you need to install 64-bit Microsoft. Ace. oledb.12.0.

Http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/58c4c61e-fa86-4809-bf7d-21bacb055d3e

File address: http://files.cnblogs.com/lyhabc/Course.rar

If something is wrong, you are welcome to make a brick o