What's the equivalent of --safe-updates of mysql in MS SQL Server?

Question

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?

Answer 1

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

• user893730: 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.
• voretaq7: If an unauthorized user somehow accesses your DB you have bigger problems. If you are working while drunk or otherwise intoxicated . . . DON'T.
• Billy ONeal: @user893730: The "unauthorized user" can just do "SET sql_safe_updates=0;". (Not to mention that they could just drop the table)
• user893730: 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
• ronenfe: 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.

Answer 2

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

• RolandoMySQLDBA: Cool feature, @gbn. I'll upvote later as I am at my limit for votes today !!!
• RolandoMySQLDBA: As promised, +1 !!!
• voretaq7: 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 :-)
• gbn: TOP is non standard.

Answer 3

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.

Answer 4

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.

• gbn: You'd actually use UPDATE TOP (4) Production.ProductInventory ... since SQL Server 2005...