Validate SQL Syntax without Committing

Previously, when I had written a SQL query and needed to validate the syntax without actually executing the query, I would wrap the query in a transaction and omit the COMMIT statement (I would use a ROLLBACK instead). This worked fine, but then I discovered the NOEXEC setting in SQL.

The NOEXEC setting allows you to set the context of your execute to On or Off. For example:

USE AdventureWorks2012
GO
-- Change Setting of NoEXEC to ON
SET NOEXEC ON;

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

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

This statement will insert zero rows into the Shift table.

Even though we have an INSERT statement right before SELECT statement, there is no impact of the INSERT statement because we have executed SET NOEXEC ON before the INSERT. When Setting NOEXEC is set to ON, it will validate the syntax of the statement but will not execute it. If there is an error it will display the error in the console.

Remember to set the value of NOEXEC statement to OFF right after your test or otherwise all of your statements will not execute on SQL Server.