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.

In MySQL, you can use the feature called --safe-updates (--i-am-a-dummy) to limit the number of rows updated per query.

http://dev.mysql.com/doc/refman/5.0/en/mysql-tips.html#safe-updates

Is there such a thing in MS SQL Server?

share|improve this question

migrated from serverfault.com Dec 7 '11 at 17:15

This question came from our site for professional system and network administrators.

4 Answers 4

There may or may not be such a thing in MS SQL, but why would you want this?

There is already a way to limit what UPDATE affects (the WHERE clause).
There is already a mechanism to safeguard your data when making changes (Transactions).

Combining the two, we get the general-case solution: "Do your updates in a transaction, with appropriate WHERE clauses to limit what they touch, and make sure the results look right before you commit":

> BEGIN;
> UPDATE mytable SET foostring='NewValue' WHERE id > 16 AND id < 32;
15 Rows Updated

> SELECT * FROM mytable;

  [Omitted -- Make sure it looks right]

> COMMIT;  --- Or ROLLBACK if the SELECT doesn't look right
share|improve this answer
    
It's useful because if an unauthorized user somehow accesses your DB, or if you are drunk or intoxicated, you can effectively limit the damage that can be caused. –  user893730 Dec 7 '11 at 18:08
4  
If an unauthorized user somehow accesses your DB you have bigger problems. If you are working while drunk or otherwise intoxicated . . . DON'T. –  voretaq7 Dec 7 '11 at 18:20
3  
@user893730: The "unauthorized user" can just do "SET sql_safe_updates=0;". (Not to mention that they could just drop the table) –  Billy ONeal Dec 7 '11 at 22:02
    
Sorry, I now remember that the reason this was put in mysql was justified as you may damage your data by mistake, hence you can limit that damage here, in cases where you don't really plan to update a great number of rows –  user893730 Jan 2 '12 at 7:55

It's per query using TOP if you want.

UPDATE TOP(100) Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;

SET ROWCOUNT has side effects on intermediate counts which gives misleading results, which is why it is slowly being deprecated. The frst MSDN example demonstartes this

Luckily, SQL Server as a grown up RDBMS has database snapshots and on-line consistent backups giving point in time recovery in case you foobar it...

share|improve this answer
1  
Cool feature, @gbn. I'll upvote later as I am at my limit for votes today !!! –  RolandoMySQLDBA Dec 7 '11 at 23:19
    
As promised, +1 !!! –  RolandoMySQLDBA Dec 8 '11 at 1:09
    
Definitely a cool feature! @gbn - out of curiosity is that part of the SQL standard, or a MS SQL extension? (I don't see any reference to it in the Postgres docs but if it's part of the standard I'll whine and for it to be implemented :-) –  voretaq7 Dec 8 '11 at 4:03
    
TOP is non standard. –  gbn Dec 8 '11 at 5:52

I don't believe so - one of the first things I learned was to write a SELECT statement first with the correct WHERE clauses to make sure it is right, and then change the SELECT to an UPDATE.

share|improve this answer

If you want to mimic that kind of behavior, there is not much you can do except limit the number of rows using TSQL.

Here is an example from the SQL Server Docs:

SET ROWCOUNT 4;
UPDATE Production.ProductInventory
SET Quantity = 400
WHERE Quantity < 300;
GO

According to http://msdn.microsoft.com/en-us/library/ms188774.aspx:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Do not use SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL).

Thus, SQL Server 2012 will not allow SET ROWCOUNT to work on the aforementioned DML.

If you are concerned about queries that can be unintentionally destructive:

  • do not run any kind of autocommit
  • do not start SQL Server in single user mode (because CHECKPOINT services is disabled, which will effectively autocommit)

Other than these things, all other features of mysql's --safe-updates is totally your responsibility.

share|improve this answer
3  
You'd actually use UPDATE TOP (4) Production.ProductInventory ... since SQL Server 2005... –  gbn Dec 7 '11 at 23:10

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.