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.
|
1 |
SELECT * A |
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.
|
1 2 |
SELECT * from A where A=@x |
In case the query contains multiple errors, it lists all error messages along with the line numbers.
- 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.
|
1 2 3 4 |
DECLARE @Emp TABLE (Empid INT); Insert into @Emp values (1) SELECT Empid FROM @Emp; Go |
You get the expected output once the query finishes.
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.
|
1 2 3 4 5 6 7 8 |
SET PARSEONLY ON; DECLARE @Emp TABLE(Empid INT); INSERT INTO @Emp VALUES(1); SELECT Empid FROM @Emp; GO SET PARSEONLY OFF; |
We did not get any output for this query.
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.
|
1 2 3 4 5 6 7 8 |
SET PARSEONLY ON; DECLARE @Emp TABLE(Empid INT); INSERT INTO @Emp VALUES(1); SELECT Empid FROM @Emp_new; SET PARSEONLY OFF; GO |
We get the error message because the table variable @Emp_new does not exist. This way, the query executes in the parse phase only.
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.
|
1 2 3 4 5 |
SET PARSEONLY ON; SELECT * FROM [AdventureWorks].[HumanResources].[test]; GO SET PARSEONLY OFF; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
--Batch 1 SET PARSEONLY ON; SELECT * FROM [AdventureWorks].[HumanResources].[test]; GO SET PARSEONLY OFF; --Batch 2 SELECT top 1 * FROM [AdventureWorks].[HumanResources].vEmployeeDepartment; GO |
It does not report an error in batch 1 (parse only) and returns output for another batch.
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.
|
1 2 3 4 5 6 7 8 9 10 |
--Batch 1 SET PARSEONLY ON; SELECT * FROM [AdventureWorks].[HumanResources].[test]; GO --Batch 2 SELECT top 1 * FROM [AdventureWorks].[HumanResources].vEmployeeDepartment; GO |
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.
|
1 2 3 4 5 6 7 8 9 |
--Batch 1 SET PARSEONLY ON; SELECT * FROM [AdventureWorks].[HumanResources].[test]; --Batch 2 SET PARSEONLY OFF; SELECT top 1 * FROM [AdventureWorks].[HumanResources].vEmployeeDepartment; GO |
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.
|
1 2 3 4 5 6 |
EXEC(N'SET PARSEONLY OFF;'); PRINT 'Parse & Execute'; GO EXEC(N'SET PARSEONLY ON;'); PRINT 'Parse & Execute - 1'; GO |
It prints a message for both the batches. PARSEONLY statement does not have any impact in this case.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC(N'SET PARSEONLY OFF;'); PRINT 'Parse & Execute'; GO EXEC(N'SET PARSEONLY ON;'); PRINT 'Parse & Execute - 1'; GO IF (1 > 0) BEGIN SET PARSEONLY ON PRINT 'Parse & Execute - 2' END; Go |
It executes both batch 1 and 2 but parse batch but only parse batch 3 due to specified PARSEONLY statement.
Now, turn off PARSEONLY in the third batch of a similar session and execute the query.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EXEC(N'SET PARSEONLY OFF;'); PRINT 'Parse & Execute'; GO EXEC(N'SET PARSEONLY ON;'); PRINT 'Parse & Execute - 1'; GO IF (1 > 0) BEGIN SET PARSEONLY OFF PRINT 'Parse & Execute - 2' END; Go |
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.
Without making any changes in queries, execute it again, and you get the following output. This time it executes all batches and prints messages.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023