Windows Server 2008

Joni Moilanen has a very nice tip about how you can use custom colors in the status bar so that you don't truncate that production table yet again :-)

Check it out here: http://blog.jemm.net/2008/08/12/sql-server-2008-management-studio-tip-status-bar-custom-colors/

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

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

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>