Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Jan 29 '12 at 6:58

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 6 down vote accepted

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:

SET IMPLICIT_TRANSACTIONS ON

SET IMPLICIT_TRANSACTIONS (Transact-SQL)

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:

ALTER TABLE FETCH REVOKE BEGIN TRANSACTION GRANT SELECT CREATE INSERT TRUNCATE TABLE DELETE OPEN UPDATE DROP

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.

share|improve this answer
    
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 '12 at 15:30
2  
Check under Tools, Options, Query Execution, SQL Server, ANSI, SET IMPLICIT TRANSACTIONS. –  RedFilter Jan 27 '12 at 15:32
    
Beat me to it! I'll give it a try and post back. Thanks! –  JoeCool Jan 27 '12 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 '12 at 15:16
add comment

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

share|improve this answer
    
Minor nitpick: it's the (Microsoft) tools that require that, not the statement as such. –  a_horse_with_no_name Jan 29 '12 at 10:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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