Jul 12, 2006

Issue (seemingly) with the SET PARSEONLY option

This was actually a question raised in a discussion forum. Thought I will write about it anyways. In a database, lets say we don't have a table called orders.

Then the following script works fine, since syntactically its right and the select statement is just parsed.

set parseonly on
select * from orders

But the following doesn't and throws an error saying "Invalid object name 'orders'"

set parseonly on
select * from orders

set parseonly off

Might seem intriguing at first glance. Well it isn't.
Well, thats because the option "set parseonly on" is set at parse time and not at run time and parsing obviously happens before the execution :)

So what actually happens is the option is set to on and then the statement is parsed and then it doesn't throw the parsing error and then the option is set to off. Now SQL Server has completed parsing the script. Now it will check for the PARSEONLY option whether to execute it or not. and since the option is off it executes the query and throw an error.
Well, just to prove my point, try and execute this

set parseonly on
select top 1 * from sysobjects
set parseonly off

You will actually get the result.

And then I know there will be some smart guy out there who will try to bring in a paradox here.
I would rather bring it myself ;)

Lets take this script

set parseonly on
set parseonly off


Now, shouldn't the second statement be parsed and not executed since parseonly is on. Well that will just mean that "parseonly" option once its set ON can never be switched off :)
Well as I said in the beginning "parseonly" is set at parse time and for clarity sake take it this way that the "set parseonly" statements will be removed during the execution time.

0 comments:

Post a Comment