SQL SERVER – What Does SET NOEXEC Do?

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.

SQL SERVER - What Does SET NOEXEC Do? compilesandrun-800x257

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)

SQL Scripts, SQL Server, SQL Server Configuration
Previous Post
Difinity 2018 – Auckland, New Zealand – Pre-Con on SQL Server Performance Tuning
Next Post
SQL Authority News – Join me at SQLBits 2018 – 36 Scripts: Performance Tricks Jedi Master Wants You to Know

Related Posts

7 Comments. Leave new

  • Hello Pinal,
    Really helpful information,but ‘SET NOEXEC ON’ only check syntax not Object.I have checked this one example.

    Reply
  • Is this same as Parse in SSMS window? @Pinal Sir

    Reply
  • 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)

    Reply
  • 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?

    Reply
  • gaurav gupta
    July 14, 2021 4:14 pm

    It’s just checked it’s validate sql query syntax but doesn’t validate objects exists or not

    Reply
  • 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

    Reply

Leave a Reply