Testing T-SQL script before execution

noexec smallTwo months ago I described how to validate syntax of Oracle RMAN commands without executing them. Today, I would like to focus on a similar feature of SQL Server.

It is very unfortunate when just after executing a script, SQL Server Management Studio reports Query completed with errors. It gets worse, when after analyzing the result you realize some of the statements were successfully executed and some did not. Then you can revert the successful ones, fix the script and execute it again. Of course, you can also reapply the failed ones without reverting the successful parts but are sure the order of the statements does not matter?

Fortunately, there is at least a partial solution to it - noexec parameter.

 

NOEXEC simple example 

Lets look at a simple example. I have the following script:

select * intoo dbo.copy
from dbo.test
go

delete from
dbo.test
go

It should create dbo.copy table from dbo.test data. Then the dbo.test table will be cleaned. Unfortunately, there is a typo in the first statement (double o in into keyword). If it is executed as is, the following results is returned:

incorrect syntax but still executed

It is important to notice - copying data failed but dbo.test table is already empty (delete statement was executed successfully - 3 rows affected).

A solution for this is the noexec parameter. By default it is set to off but it can be enabled, if you want to test a script without executing it. The parameter tells SQL Server to parse the script and that is it, no execution. Here is how it works - at the beginning of the script it can be enabled by issuing set noexec on, it is also important to disable it at the end to revert it to the default value by stating set noexec off.

set noexec on
go

select
* intoo dbo.copy
from dbo.test
go

delete from
dbo.test
go

set noexec off
go

After enabling noexec, the statements are not executed, only parsed. The output is:

Incorrect syntax but not executed

0 row(s) affected indicates nothing was deleted so there is no data loss even because of the syntax error.

Using this option in cases where premature executing a script may cause harm, can save DBAs time. Of course, all scripts should be tested on test environment prior running them on production but still ... noexec can save your time of reverting everything even on development environment.

 

Gotchas

At the end, lets take a quick look at a simple case, when it is a little more tricky. As it was stated before, noexec parses statements without executing them. It means SQL Server may not know whether referred objects exist or not. See the following example:

set noexec on
go

select
* intoo dbo.copy
from dbo.test
go

delete from
dbo.test_WRONG_NAME
go

set noexec off
go

As shown below, only the error in into keyword is reported.

Wrong object name not reported

A good news is - 0 row(s) affected message disappeared - it is a delete statement so some information about rows affected should have been printed out. It can be an indicator that something is wrong, for example a table does not exist.

This option is not an indicator of all issues in T-SQL scripts, but it is definitely useful to detect at least some of them before execution.

 

Read more on: