Rajendra Gupta
dynamic execution conditions for PARSEONLY SQL command

The PARSEONLY SQL command overview and examples

February 21, 2020 by

This article explores the uses of the SET PARSEONLY SQL command for SQL Server queries.

Introduction

Developers write complex and lengthy SQL scripts. The high-level tasks in executing a SQL query are parsed, compile and execute.

  • Parse: In this phase, SQL Server parses the syntax of a T-SQL. For a successful query execution. It should have valid syntax. It also checks for the variable declaration, query identifiers. We can also parse query using SSMS query parser(CTRL+F5)

Query parser icon might vary in a different version of SSMS. In the following screenshot, we see the query parser icon in SSMS 18.4.

For example, it highlights incorrect syntax in the following query.

incorrect syntax

incorrect syntax error

Similarly, the following query has valid syntax, but we did not declare the scalar variable @x. In the message tab, we get a message – Must declare the scalar variable @x.

Must declare the scalar variable

In case the query contains multiple errors, it lists all error messages along with the line numbers.

multiple errors

  • Compile: In this phase, SQL Server checks for the objects, whether they exist or not. It also checks user permissions. During the compilation phase, SQL Server also optimizes the query execution and comes with an optimized execution plan using available indexes and statistics
  • Execute: Once SQL Server completed parse and compilation, SQL Server executes query batch and returns output to the client

Suppose you are writing a complicated script and press F5 by mistake. You must hit the cancel button to stop this execution, but it might have done damages for you. For example, you specified a drop table statement for dropping a few tables, but if it satisfies certain conditions. We want to prevent such accidental query execution.

SQL Server provides PARSEONLY and NOEXEC session-level configuration. Let’s explore PARSEONLY in this article.

PARSEONLY SQL Command

You might have noticed we use the Go statement for query execution in SQL Server. The Go statement works as a batch separator. SQL Server processes one batch request at a time. We might use multiple batches in a single query session, but these batches are independent of each other.

We can use the PARSEONLY statement in a query batch, and SQL Server does not compile and execute the statement.

  • The PARSEONLY statement allows only the Parse phase for T-SQL. It has the following options:
    • SET PARSEONLY ON: We can specify this statement and only parse phase is executed
    • SET PARSEONLY OFF: If we have enabled PARSEONLY using SET PARSEONLY ON, we can disable the query execution behavior using SET PARSEONLY OFF
    • We can use it anywhere in a batch
    • We cannot use any conditional behavior such as Case and IF statements with PARSEONLY

Let’s understand the PARSEONLY statement behavior using examples.

Example 1: Default query behavior SQL Server


Execute the following T-SQL batch, and it completes all phases of a SQL Query, i.e., Parse compiles and execute.

You get the expected output once the query finishes.

Query output

Example 2: specify PARSEONLY SQL Command for query batch

In this example, we use SET PARSEONLY ON before query batch and SET PARSEONLY OFF after query batch finishes. As stated earlier, it should only execute the parse phase for this batch. Let’s execute this and observe the behavior.

We did not get any output for this query.

specify PARSEONLY SQL command for query batch

Example 3: specify PARSEONLY SQL Command with parsing error

In this example, we intentionally modified the T-SQL batch so that it gives an error during the parse phase.

We get the error message because the table variable @Emp_new does not exist. This way, the query executes in the parse phase only.

parsing error

Example 4: Specify PARSEONLY SQL Command with objects that do not exist

In this example, we use a select statement, but the table does not exist. You can see a red line below the object that shows it does not exist in the current database. As stated earlier, SQL Server checks for object existence during the compile phase. We specify SET PARSEONLY ON for skipping the compile and execute phase. Due to this reason, query complete parse phase and do not give any error.

objects that do not exist

Example 5: Multiple batches and PARSEONLY SQL Command behavior

Example 5a: Batch 1 with PARSEONLY statement and Batch 2 without PARSEONLY

Look at the following query. In batch 1, we used the SET PARSEONLY statement ON and disable SET PARSEONLY after batch finishes.

It does not report an error in batch 1 (parse only) and returns output for another batch.

Multiple batches in PARSEONLY SQL command

Example 5b: Batch 1 with PARSEONLY statement ON but without PARSEONLY statement OFF

In this example, we use the PARSEONLY ON statement but did not turn it off. Once we turn PARSEONLY ON, it disables the compile and executes phase for all batches in the session. Therefore, SQL Server does not execute another batch as well.

Multiple batches conditions

Let’s twist the query a little bit. In the following query, We turn on and off PARSEONLY in a single batch. We did not use the Go statement so that SQL Server can understand different batch. Once we execute this query, SQL Server does parse and skips the query execution because PARSEONLY OFF does not have any impact on the current batch.

Different condition for multiple batches for PARSEONLY SQL command

Example 5C: effect PARSEONLY statement ON with dynamic execution

Look at the below queries. We specify PARSEONLY ON (batch 1) and PARSEONLY OFF (batch 2) statements inside EXEC.

It prints a message for both the batches. PARSEONLY statement does not have any impact in this case.

dynamic execution

Now, we add another batch in this session. This batch contains PARSEONLY statement inside the IF statement. Execute these batches once in a new query window of SSMS.

It executes both batch 1 and 2 but parse batch but only parse batch 3 due to specified PARSEONLY statement.

dynamic execution conditions for PARSEONLY SQL command

Now, turn off PARSEONLY in the third batch of a similar session and execute the query.

This time it only prints a message for batch 3. Previously, we turn on PARSEONLY in this session; therefore, its impact still exits for previous batches. Due to this, SQL Server does not execute batch 1 and 2. In batch 3, we turn off PARSEONLY; therefore, it executes and prints a message.

SET PARSEONLY OFF

Without making any changes in queries, execute it again, and you get the following output. This time it executes all batches and prints messages.

Re-executee the query

Conclusion

In this article, we explored the uses of the PARSEONLY SQL Command for excluding compile and execute phase of a query. It might be useful for query debugging or test purposes.

Rajendra Gupta
222 Views