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.

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

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.
How to Compile Queries and Not Execute Them? - Interview Question of the Week #219 compilesandrun-800x257

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)

Execution Plan, SQL Scripts, SQL Server
Previous Post
How to Limit Output of Varchar(max), Nvarchar(max) in SELECT Statement? – Interview Question of the Week #218
Next Post
Does ARITHABORT Setting Negatively Impact SQL Server Performance? – Interview Question of the Week #220

Related Posts

3 Comments. Leave new

Leave a Reply