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


Is there such a thing in MS SQL Server?

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":

> 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
  • 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, 2011 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, 2011 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) Dec 7, 2011 at 22:02
  • 1
    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, 2012 at 7:55
  • Why is the language built like that, If they had required an "ALL" keyword to be added after the update or delete it will prevent accidental changes to the table. Also the management studio could watch before running the query if it's a delete or update without a where clause.
    – ronenfe
    Feb 18, 2015 at 23:28

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...

  • 1
    Cool feature, @gbn. I'll upvote later as I am at my limit for votes today !!! Dec 7, 2011 at 23:19
  • As promised, +1 !!! Dec 8, 2011 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, 2011 at 4:03
  • TOP is non standard.
    – gbn
    Dec 8, 2011 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.


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:

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

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.

  • 3
    You'd actually use UPDATE TOP (4) Production.ProductInventory ... since SQL Server 2005...
    – gbn
    Dec 7, 2011 at 23:10

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.