Two 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:
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:
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.
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: