T-SQL Tuesday: Lessons Learned the Hard Way

AaronBertrand over 6 years ago 2 minute read time

T-SQL Tuesday

This month's T-SQL Tuesday is hosted by Raul Gonzalez (@SQLDoubleG), and is about Lessons Learned the Hard Way.

Now, I've learned many lessons the hard way. When I started my career and transitioned from web design and server-side scripting into more focus on SQL Server, I bought a whole bunch of books; my favorites were those by the late Ken Henderson (I still have them). Sadly, I skimmed all of them - I preferred to learn by doing, and sometimes I had to learn really quickly. Being in a startup meant turnaround times were often rushed and haphazard. Rather than learn up front what might happen with, say, ntext and image columns at scale, I would learn much later.

I learned so many things the hard way - often after getting used to doing them in a suboptimal way - that I've assembled a lot of those things I've learned into a popular conference session ("Bad Habits to Kick"), the better part of a full-day workshop, and a long-running blog theme.

One of the hard lessons I've learned multiple times is running queries without double-checking accuracy. I'm talking about deletes or updates against the wrong environment, with an incorrect WHERE clause, or with no WHERE clause at all. Guess what happens if you accidentally delete all the rows in a table in production? If you're not log shipping on an intentional delay, you're restoring from a backup. Which is time consuming, disruptive, and error-prone itself.

So, in a VM I dedicate to production-related administration, I modified my New Query template, as I described recently in this MSSQLTips post. Basically, you open this file:

C:\Program Files (x86)\Microsoft SQL Server\[140|130|120|110]\Tools\Binn\ ManagementStudio\SqlWorkbenchProjectItems\Sql\SQLFile.sql

I changed this file so that - again, only on this particular VM - every query window I open using the New Query button on the toolbar starts out with the following code:

BEGIN TRANSACTION;
 
 
-- COMMIT   TRANSACTION;
-- ROLLBACK TRANSACTION;

Now, if I open a new window and type some hair-brained query really quickly and hit F5, I might hopefully notice when the number of rows affected is different from what I expected. In that case, I can hightlight the ROLLBACK portion and hit F5 again. Much easier than restoring from backup, right?

Of course, there are other potential issues with this. Most importantly, this makes it possible to type up a perfectly valid query, hit F5, then go to lunch. Which won't cause data loss, but it could cause enough blocking to have you dusting off your resume when you get back from lunch.

If you're prone to running queries quicker than you can proofread them, don't learn this lesson the hard way - do something to protect your production data from errant DML.