SET NOEXEC (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Compiles each query but does not execute it.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.
The execution of statements in SQL Server has two phases: compilation and execution. This setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing. It is also useful for debugging statements that would generally be part of a larger batch of statements.
The setting of SET NOEXEC is set at execute or run time and not at parse time.
The following example uses NOEXEC with a valid query, a query with an object name that is not valid, and a query with incorrect syntax.
USE AdventureWorks2012; GO PRINT 'Valid query'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Inner join. SELECT e.BusinessEntityID, e.JobTitle, v.Name FROM HumanResources.Employee AS e INNER JOIN Purchasing.PurchaseOrderHeader AS poh ON e.BusinessEntityID = poh.EmployeeID INNER JOIN Purchasing.Vendor AS v ON poh.VendorID = v.BusinessEntityID; GO -- SET NOEXEC to OFF. SET NOEXEC OFF; GO PRINT 'Invalid object name'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Function name uses is a reserved keyword. USE AdventureWorks2012; GO CREATE FUNCTION dbo.Values(@BusinessEntityID int) RETURNS TABLE AS RETURN (SELECT PurchaseOrderID, TotalDue FROM dbo.PurchaseOrderHeader WHERE VendorID = @BusinessEntityID); -- SET NOEXEC to OFF. SET NOEXEC OFF; GO PRINT 'Invalid syntax'; GO -- SET NOEXEC to ON. SET NOEXEC ON; GO -- Built-in function incorrectly invoked. SELECT * FROM fn_helpcollations; -- Reset SET NOEXEC to OFF. SET NOEXEC OFF; GO