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

By:   |   Comments (8)   |   Related: > TSQL


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?


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\

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\

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:



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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, January 21, 2020 - 11:26:10 AM - Kevin Schultz Back To Top (83910)

In SQL Server Management Studio 18.x, the file seems to have moved:

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql

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

BTW: the latest beta version of the free plugin SSMSBoost ( 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 (55792)


@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 (55790)


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 (55787)

 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 (55782)


@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 (55778)

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








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

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

get free sql tips
agree to terms