Problem
Sometimes, it is useful to find out potential errors before executing the query. In SQL Server, it is possible to check the query syntax without executing it. Moreover, it is also possible to check the syntax and determine whether the query will be compiled successfully without executing it. This can be helpful when developers deal with complex queries and want to eliminate as many errors as possible before the actual execution.
Solution
The features mentioned above can be implemented using the SET statement's PARSEONLY and NOEXEC options. This tip will discuss and compare these two features.
SET PARSEONLY ON
If we only want to check the syntax of the query, we can use the SET PARSEONLY ON statement. When it is ON, SQL Server will only parse the statements (they will not be compiled and executed). SQL Server will verify whether the code consists of valid T-SQL statements.
SET NOEXEC ON
When NOEXEC is ON, SQL Server will parse and compile the query without executing it. It means not only syntax checks will be performed, but also permissions and missing objects will be checked. Note: NOEXEC supports deferred name resolution, which means no error will be thrown if some objects referenced in the code are missing.
PARSEONLY vs. NOEXEC vs. EXECUTE
Now, we will demonstrate these features in practice.
Let’s start by creating a test environment. Run the following code in SQL Server Management Studio (SSMS):
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE TestTable(
[ID] [int] NOT NULL,
[Val] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
INSERT [dbo].[TestTable] ([ID], [Val]) VALUES (1, 10)
GO
INSERT [dbo].[TestTable] ([ID], [Val]) VALUES (2, 20)
GO
INSERT [dbo].[TestTable] ([ID], [Val]) VALUES (3, 30)
GO
In SSMS, open three query windows and paste each piece of the code below into the corresponding window:
--Query Window 1
SELECT ID, NewVal
FROM TestTable
--Query Window 2
SET PARSEONLY ON
GO
SELECT ID, NewVal
FROM TestTable
--Query Window 3
SET NOEXEC ON
GO
SELECT ID, NewVal
FROM TestTable
Then, let’s run each query. As we can see, we are referring to a non-existing column in the TestTable table.
- The first query that executes the statement fails and throws an error – Invalid column name ‘NewVal.’
- The second query, where PARCEONLY is ON, successfully parses the statement as the code is syntactically correct.
- The third query, throws the same error as the first query, as during the compilation phase, it is detected that there is no NewVal column in TestTable.
It is worth mentioning that if we refer to a non-existing table, the query where NOEXEC is ON will not throw an error because it supports deferred name resolution as mentioned above. Here is an example.
--Query Window 3
SET NOEXEC ON
GO
SELECT *
FROM NewTestTable
Note: It is also possible to parse the query by selecting it in SSMS and using Ctrl+F5 or clicking on the corresponding button on SSMS:
Now, we will modify the queries in the following way:
--Query Window 1
SELECT ID, Val
FROM TestTable
WHERE Val=@pVal
--Query Window 2
SET PARSEONLY ON
GO
SELECT ID, Val
FROM TestTable
WHERE Val=@pVal
--Query Window 3
SET NOEXEC ON
GO
SELECT ID, Val
FROM TestTable
WHERE Val=@pVal
As we can see, we are using a non-declared variable in the query, which makes the query syntactically incorrect. Thus, even the parsing fails:
In the following example, we will fix the issue with the variable declaration but will try to insert a duplicate value in the primary key column:
--Query Window 1
DECLARE @pVal INT
SET @pVal=10
SELECT ID, Val
FROM TestTable
WHERE Val=@pVal
INSERT INTO TestTable(ID, Val)
VALUES(3, 40)
--Query Window 2
SET PARSEONLY ON
GO
DECLARE @pVal INT
SET @pVal=10
SELECT ID, Val
FROM TestTable
WHERE Val=@pVal
INSERT INTO TestTable(ID, Val)
VALUES(3, 40)
--Query Window 3
SET NOEXEC ON
GO
DECLARE @pVal INT
SET @pVal=10
SELECT ID, Val
FROM TestTable
WHERE Val=@pVal
INSERT INTO TestTable(ID, Val)
VALUES(3, 40)
As a result, only the execution has thrown an error. The parsing and compilation were successful because the query is syntactically correct, and the primary key violation cannot be detected in the compilation phase (therefore, in the parsing phase as well):
Conclusion
In summary, we have explored options of parsing and compiling queries without executing them. While parsing can be useful for checking queries syntactically, compiling can reveal more problematic parts of the query, such as referring to missing columns in tables, etc.
Next Steps
For more topic-related information, check out the following links:
