As a SQL Server Developer, there are times when you want to validate the syntax of a query without actually executing it. It’s important to ensure that your syntax is valid and will work with SQL Server before running the query on a production server. In this article, we will discuss the surest way to check the validity of your SQL Server syntax.

Many developers often rely on workarounds such as relying on SQL Server’s intelligence to automatically detect syntax errors or trying out the query on a development server. However, these methods are not always accurate and can lead to unexpected results.

The most reliable way to validate SQL Server syntax is by using the NOEXEC setting. This setting allows you to set the context of your execution to either On or Off. Let’s take a look at an example using the AdventureWorks database:

USE AdventureWorks2012
GO

-- Change Setting of NOEXEC to ON
SET NOEXEC ON;

-- INSERT Statement
INSERT INTO HumanResources.Shift (Name, StartTime, EndTime, ModifiedDate)
VALUES ('Pinal', GETDATE(), GETDATE()+1, GETDATE());

-- Change Setting of NOEXEC to OFF
SET NOEXEC OFF;
GO

-- Check Table Data
SELECT * FROM HumanResources.Shift;

In the above example, we have an INSERT statement followed by a SELECT statement. However, since we have executed the SET NOEXEC ON statement before the INSERT, the INSERT statement is not executed. Instead, the syntax of the statement is validated. If there is an error, it will be displayed on the screen.

You can test this by changing the name of the table or any other part of the statement. It will throw an error, indicating that the syntax is invalid.

It’s important to remember to set the value of the NOEXEC statement to OFF after your test. If you forget to do so, none of your statements will execute on SQL Server.

The NOEXEC setting is particularly useful when debugging and validating syntax that is part of a larger query. Instead of executing the entire query, you can simply validate the syntax using the NOEXEC setting.

If you have any similar cool tips that you think I should share on the blog, please leave a comment and I will post it with due credit.