T-SQL Tuesday #156 – On Production Code

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.

:{>

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

3 thoughts on “T-SQL Tuesday #156 – On Production Code

  1. Andy, thank you for sharing your knowledge! I always appreciate learning more about built-in functions that can help identify things in the query, I can see how db_name() and original_login() would be super helpful!

    I was just wondering in your example about using db_name(), since you have the USE statement directly above it is there ever a scenario where your IF statement could somehow resolve as true?

    1. Hi David,

      Thank you for reading my post and for your kind words.

      SQL Server Management Studio (SSMS) uses the F5 key to execute selected T-SQL in a query window, or execute all T-SQL in a query window is no T-SQL is selected. This can become tricksy if selected T-SQL is not visible at the current scroll position.

      If one found oneself in the master database with Data Definition Language (DDL) statements selected, though not apparent, one could create schemas, tables, views, stored procedures, functions and whatnot in the master database – just by pressing the F5 key or clicking the Execute button in SSMS.

      Ask me how I know.

      The “If” check won’t help in this case. More often than not, though, I execute the entire script. If for some reason the new database does not exist when I believe it should (I’ve misspelled database names), T-SQL execution will not proceed to the artifacts stage (provided I’ve not misspelled the database in the “If” check portion of the code).

      It’s possible to defeat any and every safety.

      :{>

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.