I have what is probably a rather unusual question. Is there a T-SQL tool that requires using a semicolon to terminate statements? I know that it's not required to use semicolons with SQL Server 2008 R1, which is what I'm connecting to, but I actually would like to be forced to use it for running update queries. I'm sure we're all aware of the dangers of writing a query like the one below in the middle of a sql pad, and then highlighting it and running it (which is unfortunately something I tend to do a lot):

UPDATE dbo.Customers
SET ZipCode = '40223'
WHERE Name = 'Bob Loblaw'

... because you only have to miss-select your text once to nuke all your customer's zipcodes!

So, ideally, I'd like to continue doing most of my sql work in SQL Server Management Studio, but have a separate pad open (in a tool that requires semicolons) and only allow myself to write data manipulation queries over there. This would give me great peace of mind, because I'd know I could never miss-select my commonly used update queries. If I did, it wouldn't run, because it wouldn't have a semicolon.

2 Answers 2


Rather than relying on semicolons (a misplaced semicolon could also cause the problem you are describing), better to rely on transactions.

Set up your query tool to require a commit, and you will have the option rollback if necessary.

In SSMS, you can do this by checking the option SET IMPLICIT TRANSACTIONS under Tools/Options/Query Execution/SQL Server/ANSI. Or, you can turn it on with this command:



When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.

When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:


If the connection is already in an open transaction, the statements do not start a new transaction.

Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.

  • Sounds like a great idea. Any idea if it's possible to setup SSMS to require transactions? I don't see that in the options.
    – JoeCool
    Jan 27, 2012 at 15:30
  • 2
    Check under Tools, Options, Query Execution, SQL Server, ANSI, SET IMPLICIT TRANSACTIONS.
    – RedFilter
    Jan 27, 2012 at 15:32
  • Beat me to it! I'll give it a try and post back. Thanks!
    – JoeCool
    Jan 27, 2012 at 15:32
  • Started using this technique and I LOVE it! It's exactly what I was looking for -- takes almost all the anxiety away from running queries in a production environment. Thanks!
    – JoeCool
    Feb 2, 2012 at 15:16

Some T-SQL statement require the previous statements to be terminated (MERGE, WITH for a CTE) but there is no blanket requirement.

It will be added later as far as I know and it is recommended.

To avoid fubaring your data (which you can only do win production with permissions)

  • use different credentials for read and writes/deploys
  • use stored procedures where possible
  • prepare your scripts on non-production servers and deploy as whole scripts
  • take database snapshots/backups before running scripts
  • have a BEGIN TRAN first, check @@ROWCOUNT, then COMMIT
  • ...

Even though MySQL requires statements to be terminated, the last one in a MySQL batch (or highlighted code) doesn't require it. So it's back to good old developer discipline and separation of environments

  • Minor nitpick: it's the (Microsoft) tools that require that, not the statement as such.
    – user1822
    Jan 29, 2012 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.