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

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

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.