By: Aaron Bertrand | Comments (8) | Related: > TSQL
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):
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:
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
- Modify SQLFile.sql to include explicit transactions. You can also modify the file to include default comments or commonly used commands.
- Review these tips and other resources:
- Assign Colors in SQL Server Management Studio Query Windows Based on Environment
- Customizing the Status Bar for each SQL Server SSMS Connection
- SQL Server 2016 Management Studio Enhancements
- New Features in SQL Server Management Studio for SQL Server 2016
- Download Management Studio 2016 or SSMS 17.0
- All Management Studio tips
About the author
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, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
