In this article, we will look at the 2 different SQL syntax checker tools that help to find the syntax errors of the queries without executing them.
What is a SQL syntax checker?
SQL syntax checker tools validate SQL syntax or indicate the incorrect syntax errors if it exists. These tools can be helpful to determine the syntax errors without executing the whole query. The following 2 tools can be used to validate the syntax of our T-SQL queries:
What is the query parsing in SQL Server?
When we submit a query in SQL Server to execute, it performs 3 essential phases during the execution of the query:
- Parse: In this phase, the Query Parser checks and validates the syntax of the SQL statement and generates a parse tree of the query. The parse tree is sent to the next stage for processing
- Compile: In this phase, the query optimizer generates an execution plan for the query
- Execute: In this final stage, the storage engine executes the SQL statements
How to validate query syntax with SQL Server Management Studio (SSMS)
SQL Server Management Studio (SSMS) is an advanced integrated development tool to manage, administrate, and configure SQL Server and it also offers a query editor option to develop and execute T-SQL queries. We can find a Parse button on the query editor toolbar of SSMS, that only checks the syntax of the selected statement or all statements that are given by the users. So that, we can use SSMS as a SQL syntax checker tool.
Here we need to take into account that, when we parse a query the compile and execute phases are not performed. In the following example, we will check the syntax of a very simple query. To validate a query syntax consists of only 2 simple steps:
- Paste or write query into the query panel
- Click the parse button or press the Control + F5 key combination
As seen, the query syntax has been validated successfully. Now we will remove the FROM clause of the statement and re-parse the query.
After the re-parsing of the query, SQL Server returns an incorrect syntax error. Another option to check the syntax of the queries is using the SET PARSE ONLY command. This command configures the session into parsing mode.
|
1 2 3 4 5 |
SET PARSEONLY ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person |
SQL Fiddle
SQL Fiddle is an online web application that can be used to practice or share queries with their schema build script for different database systems.
Besides this, we can use SQL Fiddle as a SQL syntax checker but we need to create all objects that are placed in the query. For example in this sample query, we can build the schema and execute the query.
At the same time, it shows the execution plan of a query after its execution.
How to compile queries with no execute: SET NOEXEC ON command
After enabling the NOEXEC option for a session, SQL Server parses and compiles each statement of the query but it does not execute the query. The advantage of this command is to perform the parse and compile phases. NOEXEC option provides the deferred name resolution, so it controls only the referenced if one or more referenced objects in the batch don’t exist, no error will be thrown. We will explain this concept with a very simple example. In the example query, everything is okay because the table and columns exist and syntax is also valid
|
1 2 3 4 5 6 7 |
SET NOEXEC ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName |
In the following example, the table does not exist but the query is validated but not compiled.
|
1 2 3 4 5 |
SELECT FirstName, MiddleName,LastName FROM Person.Person_NotExist GROUP BY FirstName, MiddleName,LastName |
In this last example, SQL Server does not find the referenced objects so it will return an error.
|
1 2 3 4 5 6 7 |
SET NOEXEC ON GO SELECT FirstName1,dbo.NotExistsFunction, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName |
When we only parse the following query the result will return successfully but the syntax of the query is invalid because of the missing column names after the group by.
|
1 2 3 4 |
SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY MiddleName |
Despite that, after enabling the SET NOEXEC option, the query result will return an error.
This example shows the NOEXEC and PARSEONLY option differences. When we correct the misspelling of the syntax, SQL Server does not return any error.
|
1 2 3 4 5 6 7 |
SET NOEXEC ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName |
Another key point about the SET NOEXEC command is related to the cached execution plans. SQL Server stores the execution plan of the executed queries in the plan cache. When we execute a query after enabling the NOEXEC option and if this query is not returned any error, the execution plan of the query will be stored in the plan cache. Let’s look at this working mechanism with an example. Firstly, we will clear the plan cache data of the example query if it exists. To do this, we will execute the following query and find the plan cache details.
|
1 2 3 4 5 6 7 8 |
SELECT * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 0 AND text like '%SELECT FirstName, MiddleName,LastName FROM%' AND text NOT LIKE '%sys.dm_exec_cached_plans%' ORDER BY usecounts DESC; |
As a second step, we will drop the execution plan that is stored for the sample query. We will pass the plan handle data as a parameter to the DBCC FREEPROCCACHE.
|
1 |
DBCC FREEPROCCACHE(0x06001200A94F9D0C203F93A87B02000001000000000000000000000000000000000000000000000000000000) |
Before executing the query, we can create an extended event session to observe the query compilation event. This extended event must include the query_pre_execution_showplan. This event captures the SQL statement is compiled. At the same time, this event shows the execution plan in an XML format.
|
1 2 3 4 5 6 7 |
SET NOEXEC ON GO SELECT FirstName, MiddleName,LastName FROM Person.Person GROUP BY FirstName, MiddleName,LastName |
As we have explained, after enabling the NOEXEC command the query is compiled by the query optimizer.
Conclusion
In this article, we have looked at two different SQL syntax checker tools to validate our queries without executing them.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023