SQL Server RECOMPILE Query Option
A simple example on SQL Server’s RECOMPILE query option.
the RECOMPILE Query Option directs SQL Server to generate a new Query Execution Plan every time the query is run, the syntax for the command is:
OPTION(RECOMPILE)
Being a Query Option, the RECOMPILE statement needs to be placed at the end of the query, as such:
SELECT
Sale,
Amount
FROM
dbo.Transactions T WITH (INDEX(IDX_Customer_2))
WHERE
CustomerID = 1
OPTION(RECOMPILE)
Sale,
Amount
FROM
dbo.Transactions T WITH (INDEX(IDX_Customer_2))
WHERE
CustomerID = 1
OPTION(RECOMPILE)
Usually forcing SQL Server to regenerate a plan is needed in the following situations:
- SQL Server Query Processor engine is not generating the expected efficient plans due to plan generalization.
- Catch-all queries such as using ISNULL in the WHERE clause
0 votes
Leave a Reply
Want to join the discussion?Feel free to contribute!