What would you do if you wanted to check whether a particular query/batch you wrote is free from any errors without using Intelli-sense and without executing or compiling the statement?

It’s a fairly simple question for anyone who has worked with SQL Server for a considerable amount of time. You would use the tiny blue check-box besides the “Execute” and “Debug” buttons on the SSMS toolbar, right?

Parse button on the SSMS for SQL 2012 toolbar

SET PARSEONLY

Well, if you are a keyboard-savvy developer (like me), you would use a SET option. SET options allow us to change the behaviour of SQL Server for the current session with respect to handling of specific information like date and time values, locking, Query execution, transaction, ISO settings and Statistics.

One such option is the SET PARSEONLY option, which when set to ON, parses a T-SQL query/batch for errors without executing or compiling the query/batch.

Let’s do a simple test to confirm if this indeed works or not. After parsing the query using the PARSEONLY option, we will attempt to see if a cached plan was generated for the query. If the query compiles or gets executed, an entry would exist for the cached plan.

USE AdventureWorks2012
GO

--Set PARSEONLY to ON, indicating that the query should not be compiled or executed
SET PARSEONLY ON

SELECT '1' AS RoundNum,
       Employee.BusinessEntityID,
       Employee.BirthDate,
       Employee.Gender,
       Employee.JobTitle
FROM HumanResources.Employee AS Employee;
GO

--Set PARSEONLY to OFF
SET PARSEONLY OFF

--Check for caching of the query plan. 
--If the query was compiled, an entry would be available here
SELECT '1' AS RoundNum, usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
        text like '%FROM HumanResources.Employee%'
    AND text NOT LIKE '%Check%'
ORDER BY usecounts DESC;
GO

Execution of both the queries succeed, but no records are returned by the 2nd query (one which returns cached plans). Checking the “Messages” tab also tells us that no records were returned back to the client, indicating that the first query was only parsed, but not compiled or executed.

Please NOTE:

1. The SET option – PARSEONLY is set at parse time, and not at execution or run-time. Hence, please do not use this inside a stored procedure or a trigger
   
2. Successful parsing of a query does not protect against:




• Incorrect object names
  
• Any logical or business-rule related errors that may exist


3. Hence, before integrating your code changes into source control, please make sure that you unit test your queries/batches by setting PARSEONLY to OFF

Until we meet next time,

Be courteous. Drive responsibly.