Just the other day I received an email with a question – What Does SET NOEXEC Do?
I was really happy to read this question as I have been meaning to write a blog post about it but I was just lazy about it.
Here is the answer to the question – When SET NOEXEC ON is placed on the top of the query, the query is compiled but it does not get executed.
Let me explain a bit more in detail –
SQL Server executes any query in two phases: Compilation and Execution. During the Compilation phase, SQL Server validates the syntax and object names using your query. The default setting for any session is SET NOEXEC OFF and that is why whenever we ran any query it is executed right after the compilation of the code.
Let us understand how it works with the help of an example.
First, run following query in SSMS
SET NOEXEC ON SELECT 1
You will notice that query gets executed but there is no resultset. The reason is actually the script is just compiled and the query has not been executed.
Now run the following query in SSMS
SET NOEXEC OFF SELECT 1
In this case, once the query is executed, it produces the result as it goes through both the phases of query execution.
I hope this explanation was simple enough and if you have any further suggestions, please add in the comment sections.
Reference: Pinal Dave (https://blog.sqlauthority.com)