THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Tip, Compiling Your SQL Without Running It To See If It Would Run

Let's say you have a big SQL script with a ton of code and you want to make sure it runs but you don't want to execute it because it updates tables, deletes data etc etc.
Take this simple example

SELECT GETDATE()

GO

SELECT 1/asasasas

GO


You can probably guess that the second statement is not valid, when you have a lot of code it is more difficult to spot these things.
Execute the code above and you will get this


(1 row(s) affected)

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.

SQL Server has the SET NOEXEC statement. From BOL:

When SET NOEXEC is ON, Microsoft® 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 consists of 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 usually 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.


So execute the code below

SET NOEXEC ON

GO

SELECT GETDATE()

GO

SELECT 1/asasasas

GO

SET NOEXEC OFF

GO

 

As you can see the output is the following:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'asasasas'.


You never see the getdate. Parsing that code will not throw an error and because of deferred name resolution you can fat-finger table names and it will parse without a problem. Using NOEXEC is a nice way of seeing if your code would run. The question is, of course, whether you need to do this at all since we all have a QA server <g>

 

Published Wednesday, August 13, 2008 12:19 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

Aside from the impact to data, even if you just have a bunch of SELECTs, you may want to use NOEXEC so that you don't have to wait for the data to actually be returned.

August 13, 2008 11:38 AM
 

Denis Gobo said:

Aaron,

yep, beats adding WHERE 1= 0 to every query  :-)

August 13, 2008 12:28 PM
 

Diana said:

Great tip, thank you...

April 13, 2009 8:51 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement