Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Safeguard to Avoid Costly Mistakes for UPDATE or DELETE Statements in SQL Server Management Studio


By:   |   Read Comments (7)   |   Related Tips: More > T-SQL

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Iíve talked to many people who have opened up a new query window in SQL Server Management Studio, fired off an UPDATE or DELETE statement, and then once the query finished, they realized that they forgot the WHERE clause (or forgot to highlight it). The story usually then takes a turn for the worse: they had to restore from backup or, perhaps due to lack of backups, they had to dust off their resume. Iím sure many people have frantically pressed the Undo button on the SSMS toolbar, hoping it would affect their query instead of the text editor. Wouldnít it be nice if there was an Undo Last Query button?

Solution

SQL Server doesnít support Ctrl + Z, but you can protect yourself in other ways. A previous tip showed how you can change the color of your status bar, making it more obvious that you are connected to critical environments; I can tell you from personal experience that, sometimes, that isnít enough.

Another way you can protect yourself Ė and which I started doing about a decade ago Ė is to make sure that all new query windows start with an explicit transaction. You can do this by modifying the new query template, which pulls from a file named SQLFile.sql. The location of this file will vary by version of SSMS; on my machine, since Iím running the latest (17.0), the file is in:

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\
    ManagementStudio\SqlWorkbenchProjectItems\Sql

For older supported versions, you will likely have to change 140 to 130, 120, or 110. On really ancient versions of Management Studio, you should just upgrade. But back then the location was something like:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\
    VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql

And similarly, you may have to change 100 to 90 if you are using SQL Server 2005.

If you navigate to this folder and open SQLFile.sql, youíll see that itís empty. Before you do anything, you should right-click the file and ensure that it is not read only, and that users can modify the file (which may require additional prompts from UAC, if that is enabled):

SQLFile.sql Properties - Description: SQLFile.sql Properties

Once you can modify the file, you can simply add the following lines of code, and then save:

BEGIN TRANSACTION;

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Now when you click the New Query toolbar button, or Ctrl + N, this is what you should see:

Modified New Query Template - Description: Modified New Query Template

Note: Modifying this file does not affect the right-click context menus in Object Explorer, even though those are also called New Query. If you feel this would be worth making consistent (I do!), please vote for this Connect item Ė even though itís closed. Comments are still accepted too, and they carry more weight than votes anyway.

So you might ask, how does this help you? Well, if you open a new query window quickly, and type up a query and hit F5 or Ctrl + E, you can validate that the right data was affected before highlighting the COMMIT TRANSACTION portion and executing that. If youíve made a mistake, you can highlight the ROLLBACK TRANSACTION portion instead, so that the only damage youíve caused was for the duration of the transaction being active.

Of course, this can cause a different problem: You run your query in this transaction, forget to commit, and then go to lunch or go home for the day. This means that you will hold all of the locks youíve taken, potentially until someone manually kills your session. So be careful out there!

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, May 16, 2017 - 4:30:49 AM - Thomas Franz Back To Top

BTW: the latest beta version of the free plugin SSMSBoost (http://www.ssmsboost.com/social/posts/m12516-SSMSBoost-v3-0-Beta--2008-2012-2014-2016#post12516) has now an open-transaction-guard that shows you a little post-it note on your screen, when there is an open transaction in one of your sessions.

This could be helpful, when you tend to forgot the commit :-)


Monday, May 15, 2017 - 7:51:11 PM - Aaron Bertrand Back To Top

 

@Aldopaolo I don't know if I like that structure. Now you're going to default to rolling back every transaction you start?

I think I'm less likely to realize I've just rolled back a transaction than I am to need to rollback a mistake. If your strategy is to always run the same transaction twice - once to confirm the rowcount, and then again to actually run it and commit, that's cool - but I don't think you want that to be the default all the time in a highly concurrent system. YMMV.


Monday, May 15, 2017 - 5:22:09 PM - Aldopaolo Palareti Back To Top

 

I prefer:

 

  begin transaction

 

  rollback transaction /*

  commit transaction -- */

 

default is rollback; for commit, I comment rollback with "--"

 


Monday, May 15, 2017 - 2:49:28 PM - Wayne Back To Top

 I loved the undo feature in Oracle, was quite surprised when I switched to MS. Since I'm lazy, I mean busy, this is a pretty neat way of defaulting the rollback syntax, thanks!

 


Monday, May 15, 2017 - 12:12:18 PM - Aaron Bertrand Back To Top

 

@Thomas I don't like that option because it's hidden and easy to forget. Changing the new query template is very in your face. But hey, I'm not suggesting my approach is the only way, just *A* way.


Monday, May 15, 2017 - 9:58:52 AM - Frank Wright Back To Top

 I do something similar at the top of script files that contain a smorgasbord of statements that I individually highlight for execution.

SET NOEXEC ON; 

--SET NOEXEC OFF;

GO

RETURN 

 

 

 


Monday, May 15, 2017 - 3:12:38 AM - Thomas Franz Back To Top

would it not help just to enable the implicit transactions in the query options (either for a particualar query or in the global option)?


Learn more about SQL Server tools