I have written up two examples when a SET ROWCOUNT command
breaks a seemingly working stored procedure or trigger. Note that currently the
best practice is to use TOP clause instead of SET ROWCOUNT, which is
deprecated
in SQL Server 2008.
However, even if you never use SET ROWCOUNT yourself, some legacy code can still use it – and it can change the behavior of your stored procedure or trigger,
unless you are careful.
Prerequisites
Here are the table and sample data:
CREATE TABLE
Data.Objects(ObjectID
INT NOT NULL,
SizeInInches FLOAT,
WeightInPounds FLOAT
);
GO
INSERT INTO
Data.Objects(ObjectID,
SizeInInches,
WeightInPounds)
SELECT 1,
10, 10 UNION ALL
SELECT 2,
12, 12 UNION ALL
SELECT 3,
20, 22;
GO
When SET ROWCOUNT breaks a stored procedure
Consider the following stored procedure:
CREATE PROCEDURE
Readers.SelectObjectsBySizeRange
@SizeFrom FLOAT,
@SizeTo FLOAT
AS
BEGIN
SELECT ObjectID,
SizeInInches,
WeightInPounds
FROM Data.Objects
WHERE SizeInInches BETWEEN @SizeFrom AND
@SizeTo;
END;
Apparently the stored procedure works:
EXEC Readers.SelectObjectsBySizeRange
@SizeFrom =
10,
@SizeTo =14
ObjectID SizeInInches WeightInPounds
-----------
---------------------- ----------------------
1 10 10
2 12 12
(2 row(s) affected)
but a SET ROWCOUNT command issued before it is invoked
breaks it. See for yourself:
SET ROWCOUNT
1;
EXEC Readers.SelectObjectsBySizeRange
@SizeFrom =
10,
@SizeTo =14
ObjectID SizeInInches WeightInPounds
-----------
---------------------- ----------------------
1 10 10
(1 row(s) affected)
The robust way is not to make unnecessary assumptions and to
make sure that SET ROWCOUNT setting is cleared at the beginning of the stored
procedure, as follows:
ALTER PROCEDURE
Readers.SelectObjectsBySizeRange
@SizeFrom FLOAT,
@SizeTo FLOAT
AS
BEGIN
SET ROWCOUNT
0;
SELECT ObjectID,
SizeInInches,
WeightInPounds
FROM Data.Objects
WHERE SizeInInches BETWEEN @SizeFrom AND
@SizeTo;
END;
Rerun the query and see for yourself that now it works:
SET ROWCOUNT
1;
EXEC Readers.SelectObjectsBySizeRange
@SizeFrom =
10,
@SizeTo =14
ObjectID SizeInInches WeightInPounds
-----------
---------------------- ----------------------
1 10 10
2 12 12
(2 row(s) affected)
When SET ROWCOUNT breaks a trigger
Consider the following trigger that logs changes into
another table:
CREATE TABLE
Data.ObjectsChangeLog(ObjectID
INT NOT NULL,
ChangedColumnName VARCHAR(20) NOT NULL,
ChangedAt DATETIME
NOT NULL,
OldValue FLOAT
NOT NULL
);
GO
CREATE TRIGGER
Objects_UpdTrigger
ON Data.Objects
FOR UPDATE
AS
BEGIN
INSERT INTO
Data.ObjectsChangeLog(ObjectID,
ChangedColumnName,
ChangedAt,
OldValue)
SELECT i.ObjectID, 'SizeInInches', CURRENT_TIMESTAMP, d.SizeInInches
FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID
WHERE i.SizeInInches
<> d.SizeInInches
UNION ALL
SELECT i.ObjectID, 'WeightInPounds', CURRENT_TIMESTAMP, d.WeightInPounds
FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID
WHERE i.WeightInPounds
<> d.WeightInPounds;
END
Apparently it works:
SET ROWCOUNT
0;
BEGIN TRAN
UPDATE Data.Objects
SET SizeInInches = 12,
WeightInPounds =
14
WHERE ObjectID = 1;
SELECT *
FROM Data.ObjectsChangeLog;
ROLLBACK;
ObjectID ChangedColumnName ChangedAt OldValue
-----------
-------------------- ----------------------- ----------------------
1 SizeInInches 2009-03-21 21:47:44.200 10
1 WeightInPounds 2009-03-21 21:47:44.200 10
However, SET ROWCOUNT breaks it. See for yourself:
DELETE FROM
Data.ObjectsChangeLog;
SET ROWCOUNT
1;
BEGIN TRAN
UPDATE Data.Objects
SET SizeInInches = SizeInInches + 1,
WeightInPounds =
WeightInPounds + 1
WHERE SizeInInches = 12;
ROLLBACK;
SET ROWCOUNT
0;
SELECT *
FROM Data.ObjectsChangeLog;
ObjectID ChangedColumnName ChangedAt OldValue
-----------
-------------------- ----------------------- ----------------------
2 SizeInInches 2009-03-21 21:49:02.200 12
(1 row(s) affected)
Although two columns were changed, only one change is
logged, which is a bug. Again, the fix is to explicitly set ROWCOUNT to zero in
your trigger, as follows:
ALTER TRIGGER
Data.Objects_UpdTrigger
ON Data.Objects
FOR UPDATE
AS
BEGIN
SET ROWCOUNT
0;
INSERT INTO
Data.ObjectsChangeLog(ObjectID,
ChangedColumnName,
ChangedAt,
OldValue)
SELECT i.ObjectID, 'SizeInInches', CURRENT_TIMESTAMP, d.SizeInInches
FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID
WHERE i.SizeInInches
<> d.SizeInInches
UNION ALL
SELECT i.ObjectID, 'WeightInPounds', CURRENT_TIMESTAMP, d.WeightInPounds
FROM inserted i JOIN deleted d ON i.ObjectID = d.ObjectID
WHERE i.WeightInPounds
<> d.WeightInPounds;
END
Rerun the update again and see for yourself that both
changes were captured. As you have seen, explicitly adding SET ROWCOUNT 0 at the
beginning of your Transact-SQL modules increases their robustness.
This post continues my series on defensive database programming. Here is my next post:
Defensive database programming: fun with triggers.
Here are
my previous posts from the series:
Summarizing previous posts about defensive database programming
Defensive database programming: SET vs. SELECT.
Stress
testing UPSERTs
Defensive
database programming: fun with UPDATE.
Defensive
database programming: eliminating IF statements.
Defensive
database programming: fun with changing column widths.
Avoid
mixing old and new styles of error handling.
Defensive
database programming: adding ESCAPE clauses.
Defensive
database programming: qualifying column names.
Defensive
database programming: rewriting queries with NOT IN().