I prefer all code to be idempotent, a $3 word that means whenever the code is executed, the post-execution state is predictable (even if the initial state is not predictable). That’s what idempotent means to me, anyway.
While “Commands completed successfully” is helpful to know – and definitely better than an error message – it does not inform the person executing the command precisely what just happened. Rather, “Commands completed successfully” informs the user that “something happened,” and as far as SQL Server Management Studio is concerned, whatever it was succeeded.
I prefer more details.
Create Database DatabaseOne
If I execute the T-SQL statement in SQL Server Management Studio connected to an instance of Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) – 15.0.2095.3 (X64):
Create Database DatabaseOne, I may get any of a number of responses depending on conditions beyond the scope of this post. Assuming conditions are nominal, two possible responses are:
- Commands completed successfully.
- Msg 1801, Level 16, State 3, Line 13
Database ‘DatabaseOne’ already exists. Choose a different database name.
If I want more information, I might begin by adding some print statements, such as:
print 'DatabaseOne database'
If Not Exists(Select [name]
Where [name] = N'DatabaseOne')
print ' - creating DatabaseOne database'
Create Database DatabaseOne
print ' - DatabaseOne database created'
print ' - DatabaseOne database already exists.'
The first time I execute this statement, I receive a message similar to:
The second time I execute this statement, I receive a message similar to:
Avoid Populating the master database
To avoid adding objects to the master database, I may want to check to see if I’m in the DatabaseOne database before proceeding, using T-SQL similar to the following:
If (db_name() <> N'DatabaseOne')
print 'NOT using DatabaseOne'
Set NoExec ON;
print 'Using DatabaseOne'
The secret sauce in this collection of statements is “Set NoExec ON;”. Setting NoExec on stops T-SQL execution in SSMS. Please note, all attempts to execute T-SQL in this query window will result in no T-SQL being executed until you set NoExec OFF. If you decide to implement this method of execution control in your SSMS T-SQL, be sure to add a “Set No Exec OFF;” statement near the top of your statement list:
If NoExec is already turned off, no harm, no foul.
You can identify yourself, the date and time the T-SQL is executed, and the target server instance using the following T-SQL at the top of your code:
declare @Now nvarchar(36) = Replace(Convert(nvarchar(18), GetDate(), 104), '.', '/') + ' ' + Convert(nvarchar(18), GetDate(), 108)
Executed By: '+ original_login() + '
Execution Date / Time: ' + @Now + '
SQL Server Instance: ' + @@servername + '
The results will display something akin to:
I often refer to this kind of information as “instrumentation.” It is documentation that is generated when the code is executed, and it surfaces information about execution(s) – even executions for deployment. I recommend copying the instrumentation to “notes” fields in tickets before closing the ticket.
The stuff I share here is a subset of practices and instrumentation I regularly add to production code. I hope this helps.