Select number of deleted records with t-SQL

Question

I can't seem to figure out how to select the number of previously deleted records with SQL Server 2008. Is it something like this?

DELETE FROM [table] WHERE [id]=10
SELECT SCOPE_IDENTITY()

Answer 1

Use SELECT @@ROWCOUNT immediately after the DELETE statement. You can read more about @@ROWCOUNT on MSDN:

@@ROWCOUNT

Returns the number of rows affected by the last statement.

Remarks

...

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

Note that I say "immediately after" because other statements can change the value of @@ROWCOUNT, even if they don't affect rows, per se:

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

...

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Answer 2

You can also SET NOCOUNT OFF.

Remarks

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.