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)
7 Comments. Leave new
Hello Pinal,
Really helpful information,but ‘SET NOEXEC ON’ only check syntax not Object.I have checked this one example.
Oh. Okay. Thanks for sharing.
Is this same as Parse in SSMS window? @Pinal Sir
Hi thanks for this usefull information. I’m new to SQL Server (I recently completed a few courses and I am eager to start working with it) How this option “SET NOEXEC ON ” could be usefull in a query or in a developer instance? sorry for my english, it is a little bit rusty (i’m from Argentina)
The code is not executed even if we put a ‘GO’ on the script. In other words, it doesn’t allow any execution from the current connection until we set to off.
Is it true?
It’s just checked it’s validate sql query syntax but doesn’t validate objects exists or not
Hi Pinal Dave – BUT If the query is not executed with SET NOEXEC ON , why is it so slow to compile a simple query against a large table (2.1 billion rows) where an index exists on clientid?
SET NOEXEC ON
go
select 1 from audittrail (NOLOCK) WHERE clientid = ‘x’
go