THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Defensive database programming: fun with ROWCOUNT

 

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

Published Saturday, March 21, 2009 10:59 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

daveballantyne said:

Is Break not the wrong word ? :)  "Causes unanticipated behaviour" would be better since SQLServer is working exactly as documented.  

Also by adding the "Set rowcount 0" to a proc, you may 'break' the calling proc if that still expects the original ROWCOUNT value.

March 23, 2009 5:19 AM
 

Alexander Kuznetsov said:

Dave,

I agree "break" is not the best word, changed it. I do not see SET ROWCOUNT inside a stored procedure having any effect outside it:

CREATE VIEW dbo.TestView

AS

SELECT 2 AS n UNION SELECT 3;

GO

CREATE PROCEDURE dbo.TestProc

AS

SET ROWCOUNT 0;

SELECT n FROM dbo.TestView;

GO

SET ROWCOUNT 1;

EXEC dbo.TestProc;

SELECT n FROM dbo.TestView;

n

-----------

2

3

(2 row(s) affected)

n

-----------

2

(1 row(s) affected)

Can you post a repro that "'breaks' the calling proc if that still expects the original ROWCOUNT value"

March 23, 2009 9:24 AM
 

daveballantyne said:

I stand corrected on that.

Ive always had it at the back of my mind that the scope of Set Rowcount was on the connection.  ie it would keep its values until the next "SET ROWCOUNT".  Maybe that was true on an ancient (6.5 ?) version but as per your script not in 2005.

March 23, 2009 10:17 AM
 

Catelu said:

Rowcount doesn't break anything. It's kind of similar to the collation of the DB. Making assumptions on that one is equally dangerous.

April 2, 2009 3:40 AM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

May 11, 2009 9:19 PM
 

Alexander Kuznetsov said:

My query used to work, but it blows up after I have added an index? The following query is not safe:

July 11, 2009 11:14 PM
 

Alexander Kuznetsov said:

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order -

July 16, 2009 5:41 PM
 

Alexander Kuznetsov : Summarizing previous posts about defensive database programming said:

October 9, 2009 4:50 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement