How to Compile Queries and Not Execute Them? – Interview Question of the Week #219

Question: How to Compile Queries and Not Execute them?

Answer: Have you ever heard this question before? Honestly, I have heard it before and I even wrote about this on this blog many years ago. Here are two of the earlier example.

• SQL SERVER – What Does SET NOEXEC Do?
• SQL SERVER – How to Validate Syntax and Not Execute Statement – An Unexplored Debugging Tip

If you read the above titles, I am sure you understood that the solution is using the command SET NOEXEC and it validates the syntax but does not execute a statement. Let us see a simple example of the same.

First, run following query in SSMS

SET NOEXEC ON
SELECT 1

You will notice that the 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.

Let me know if you know any such cool trick and I am willing to post on the blog with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)