SQL Server: SET NOEXEC ON prevent the accidently execution of entire SQL script

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

I just recently got to know about the SET NOEXEC option of SQL Server which is available since SQL Server 7.0.

As a DBA or DB Developer, we are preparing a database change script with all the database changes that we have made earlier and that SQL script needs to be executed on different database servers like development server, validation server, production server.

An earlier, I faced an issue like accidently DB developer or system admin executed entire SQL Script. The script was tested very well and put all the commands in the sequence, but then also it needs to be review before actual execution especially in production server.

You can disable the execution of your batch script by adding SET NOEXEC ON in your SQL script.

When SET NOEXEC is ON, 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.

Let me demonstrate this,

Execute below few prints:

Now execute same prints after SET NOEXEC to ON:
You cannot see any of the print messages.
Now execute below batch:
You can see last print message which is after SET NOEXEC to OFF.