This month’s T-SQL Tuesday is hosted by Tom Zika. Tom’s topic is a good one: “Which quality makes code production grade?” I can hear data minds thinking, “It depends.”

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:

1. Commands completed successfully.
2. 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:


Use [master]
go

print 'DatabaseOne database'
If Not Exists(Select [name]
From [sys].[databases]
Where [name] = N'DatabaseOne')
begin
print ' - creating DatabaseOne database'
Create Database DatabaseOne
print ' - DatabaseOne database created'
end
Else
begin
print ' - DatabaseOne database already exists.'
end
print ''

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:

Use DatabaseOne
go

If (db_name() <> N'DatabaseOne')
begin
print 'NOT using DatabaseOne'
Set NoExec ON;
end
Else
begin
print 'Using DatabaseOne'
end
print ''

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.

“Who Are You? (Language warning)“

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)
print N'
/******************************************/
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.

Conclusion

The stuff I share here is a subset of practices and instrumentation I regularly add to production code. I hope this helps.

:{>