#0204 – SQL Server – SET options – PARSEONLY – Parse a T-SQL query/batch but do not compile or execute it


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?







image
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.


image


image


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.

Advertisements

3 thoughts on “#0204 – SQL Server – SET options – PARSEONLY – Parse a T-SQL query/batch but do not compile or execute it

  1. Ritesh Shah

    Nakul,

    This is really good tip. This SET option is one of my favorite option whenever I am doing anything on the production server. I am being lazy to write this statement while working on beta or development server

    Like

    Reply
  2. Nakul Vachhrajani

    Thank-you, Ritesh for your feedback! (Yes, we are all a little lazy when it comes to development servers, aren’t we? 🙂 )

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s