Difference between RECOMPILE query hint and WITH RECOMPILE option

          

Interview questions

SQL Server - RECOMPILE query hint and WITH RECOMPILE option

Next>>         SQL Server tutorial  

SQL Server - RECOMPILE query hint and WITH RECOMPILE option - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

Explain the difference between RECOMPILE query hint and WITH RECOMPILE option.

RECOMPILE query hint can beaded to a stored procedure as query hint. This allows only that query (not all) in the stored procedure to recompile. WITH RECOMPILE option recompiles the whole stored procedure which might not be needed/beneficial if the stored procedure has many queries and only some of them need to be recompiled.

SQL Server - RECOMPILE query hint and WITH RECOMPILE option - May 05, 2009 at 22:00 PM by Rajmeet Ghai

Explain the difference between RECOMPILE query hint and WITH RECOMPILE option.

When a stored procedure is created using WITH RECOMPILE option, the execution plan for the procedure is not reused. It is created each time during execution. If the stored procedure is created using RECOMPILE query hint, only the specified queries can be recompiled not all. The query within the stored procedure that needs to be recompiled is specified as RECOMPILE.

SQL Server - RECOMPILE query hint and WITH RECOMPILE option - June 21, 2009 at 09:00 AM by Amit Satpute

Explain the difference between RECOMPILE query hint and WITH RECOMPILE option.

  • The RECOMPILE can be added to a stored procedure for a particular query within it.
  • Due to this, all the queries in the stored procedure need not be recompiled.


Next>>

Also read

SQL Server Optimization Tips

Answer - Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic......

Having clause and Where clause

Answer - Both are used for filtering of records based on filtered criteria.....

Define Distributed Query and Linked Server?

Answer - Distributed Query is a query which can retrieve data from multiple data sources including distributed data........

Querying and modifying XML data in SQL Server 2005

What is XQuery language?
Explain the syntax rule of XQuery language.
XQuery expression contains two parts: the Prolog and the Body. Explain them
Explain PATH expression in XQuery with an example................

Explain the concepts and capabilities of query parallelism.

Parallelism is the process of executing queries in parallel to increase performance.............



Write your comment - Share Knowledge and Experience


 

 
Latest placement tests
Latest links
 
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring