ah-hoc

[amitguitarplayer]

hi guys,
i know this is a very stupid question. but what the hell does Ad-Hoc mean ?
i know what a stored procedure is ! but what is ad-hoc ! ?

thank you soo much, i know i know, i need a slap across my face for even asking this question ! lolol

 

[MattP]

It's latin meaning "for this".

I'd suggest in the future that you use Google. Search using "define:<term>".

 

[amitguitarplayer]

oo no i know what "Ad hoc" means but i was looking for an example !? like what is the difference between ad hoc and a stored procedure! ?

 

[MattP]

When SQL runs a query it calculates the most efficient way to process the query and stores it in an execution plan. When you call an inline query the execution plan is generated each time the query is run. With a stored procedure the execution plan is pulled from memory and doesn't need to be recalculated.

An ad-hoc query is only meant to be used for the task at hand while a stored procedure has its DML & DDL stored in one centralized location so you can use it from multiple applications.

Stored Procedures can also be used to standardize error handling by using the RAISEERROR function to help seperate data access errors from .NET programming errors.

 

[JohnH]

When you call an inline query the execution plan is generated each time the query is run.

That is a common misconception, since Sql Server 2000 all queries are matched against existing execution plans. According to documentation this is also true for Sql Server 6.5 and earlier, but with less efficiency. With 6.5 and earlier there was another difference, SPs were partially precompiled which gave a small performance benefit, but from 7.0 SPs are compiled at execution time like other queries. This is probably the reason for many with old knowledge saying the same as you, they didn't understand the difference between caching, compiling and executing.