@@ROWCOUNT = 1 after empty MIN() query

Ran across a surprising SQL Server thing today: I was updating a query If you run a query that returns no rows and use the MIN() aggregate function, you get an empty resultset as expected, but the @@ROWCOUNT variable is set to 1. If you write the query as a TOP(1) query in a situation where it returns no rows though, you still get an empty resultset but @@ROWCOUNT is set to zero as expected.

Example of this in action (tested on SQL Server 2012):

CREATE TABLE MyTable(MyId INTEGER, MyName NVARCHAR(128));
SELECT MIN(MyId)
FROM MyTable
WHERE MyName LIKE 'MyTag'
IF @@ROWCOUNT <> 0 PRINT 'Nonzero row count'
ELSE PRINT 'Zero row count'
--Output: Nonzero row count
SELECT TOP(1) MyId
FROM MyTable
WHERE MyName LIKE 'MyTag'
ORDER BY MyId
IF @@ROWCOUNT <> 0 PRINT 'Nonzero row count'
ELSE PRINT 'Zero row count'
--Output: Zero row count
DROP TABLE MyTable;
Advertisements

  1. Leave a comment

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