Esat Erkec
SQL Fiddle can show the execution plan of a query

SQL Syntax Checker Tools

October 27, 2021 by

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.

How to use the parse button in SQL Server Management Studio

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

Validate the syntax of a query in SQL syntax checker

As seen, the query syntax has been validated successfully. Now we will remove the FROM clause of the statement and re-parse the query.

Checks the syntax of a query in SSMS

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.

Usage details of the SET PARSEONLY command

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.

How we can use SQL Fiddle as SQL Syntax checker

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.

SQL Fiddle can show the execution plan of a 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

SET NOEXEC ON function usage

In the following example, the table does not exist but the query is validated but not compiled.

SET NOEXEC ON usage for non-exists tables

In this last example, SQL Server does not find the referenced objects so it will return an error.

SET NOEXEC ON returns an error

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.

Parsing T-SQL query without execute

Despite that, after enabling the SET NOEXEC option, the query result will return an error.

SET NOEXEC ON command returns 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.

How the SET NOEXEC ON command works

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.

Listing cached execution plans in SQL Server

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.

Dropping a single cached plan of a query

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.

Using the Extended Events to monitor a query compilation

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.

Esat Erkec
Latest posts by Esat Erkec (see all)
5,005 Views