Problem

Sometimes, you would like to speed up DELETE operation on a large table you use SET ROWCOUNT <number of rows will be deleted> statement to decide number of rows that you want to delete when DELETE statement runs. However, you maybe encounter one issue of SELECT statement, it returns incorrect data rows after deleting data.

For demonstration purpose, I created table Employee as below

CREATE TABLE Employee
(
EmployeeID INT IDENTITY(1,1),
EmployeeFirstName nvarchar(50),
EmployeeLastName nvarchar(50),
Salary money,
)

Go
INSERT INTO Employee
VALUES('Henry','William',1000),('Joe','Phil',2000),('Jack','Nielse',1000)

Then we need to delete Employee has FirstName = ‘Joe’ and query Employee has Salary = 1000

BEGIN TRAN
WHILE 1 =1 
BEGIN
  SET ROWCOUNT  1;
  DELETE FROM Employee WHERE EmployeeFirstName = 'Joe'

  IF @@ROWCOUNT = 0
     BREAK;
END

ROLLBACK;

SELECT * FROM Employee WHERE Salary =1000

It was supprised, SELECT just returned one recored for Employee Henry William instead of 2 records (Henry and Jack)

EmployeeID  EmployeeFirstName                                  EmployeeLastName                                   Salary
----------- -------------------------------------------------- -------------------------------------------------- ---------------------
1           Henry                                              William                                            1000.00

 

Solution

The reason why SELECT statement returns incorrect data rows because SET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned. So we need to reset value of ROWCOUNT to the default value to avoid this issue.

BEGIN TRAN
WHILE 1 =1 
BEGIN
  SET ROWCOUNT  1;
  DELETE FROM Employee WHERE EmployeeFirstName = 'Joe'

  IF @@ROWCOUNT = 0
     BREAK;
END

ROLLBACK;
SET ROWCOUNT 0;
SELECT * FROM Employee WHERE Salary =1000 


-- 2 records were returned

EmployeeID  EmployeeFirstName                                  EmployeeLastName                                   Salary
----------- -------------------------------------------------- -------------------------------------------------- ---------------------
1           Henry                                              William                                            1000.00
3           Jack                                               Nielse                                             1000.00

Another approach, you should use TOP syntax instead of ROWCOUNT avoiding unexpected result.

DECLARE @rowcount INT = 0
BEGIN TRAN
WHILE 1 =1 
BEGIN  
  DELETE TOP (1) FROM Employee WHERE EmployeeFirstName = 'Joe'
  SET @rowcount = @@ROWCOUNT
  IF @rowcount = 0
     BREAK;
END
ROLLBACK;
SELECT * FROM Employee WHERE Salary =1000

 

Note 

As Microsoft’s recommendation, SET ROWCOUNT will not affect to DELETE,INSERT and UPDATE in a furture realase of SQL Server so you should avoid using SET ROWCOUNT with DML statements. For more detail https://msdn.microsoft.com/en-us/library/ms188774(v=sql.120).aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s