If I’m doing any manual work with T-SQL, I always begin every set of data change operations with BEGIN TRAN and I have the COMMIT TRAN commented out at the end of my script. Why?
Quite simply, because I’m wary of having a DELETE clause without a proper WHERE clause. I’ve made that mistake in my career and it’s easy to do if you are tired, pressed for time, or both. This allows me to verify I’ve only changed what I intended and then I highlight and execute the COMMIT TRAN to finish the operation. However, there have been times when I neglect this habit.
Which is why seeing Steve Jones’ post about a new feature in SQL Prompt made me smile. If you have it installed and are about to execute a DELETE without a WHERE clause, it prompts you to verify this was your intent. As Steve writes, sometimes it is. But when it isn’t, that warning can be a life saver.
I won’t abandon my BEGIN TRAN habit, but I will certainly take advantage of this new feature in SQL Prompt.