Thursday, September 27, 2012


A little known (at least by the people I've talked to) feature of Transact SQL (the Microsoft SQL engine) is the setting of ROWCOUNT. Usually ROWCOUNT is used to get the number of rows an operation has returned or affected and it is actually @@ROWCOUNT. Something like this:
UPDATE MyTable SET Value = 10 WHERE [Key]='MySetting'
SET @RowsUpdated =  @@ROWCOUNT

Instead, setting ROWCOUNT tells the SQL engine to return (or affect) only a specified number of rows. So let's use the example before:
UPDATE MyTable SET Value = 10 WHERE [Key]='MySetting'
SET @RowsUpdated =  @@ROWCOUNT
In this case a maximum of one row will be updated, not matter how many rows exist in the table with the value in the Key column 'MySetting'. Also, @@ROWCOUNT will correctly output 1 (or 0, if no rows exist).

Now, you will probably thing that setting ROWCOUNT is equivalent to TOP and a lot more confusing. I had a case at work where, during a code review, a colleague saw two SELECT statements one after the other. One was getting all the values, with a filter, and another was selecting COUNT(*) with the same filter. He correctly was confused on the reason why someone would select twice instead of also selecting the count of rows returned (or using @@ROWCOUNT :) ). The reason was that there was a SET ROWCOUNT @RowCount which restricted the number of rows returned by the first SELECT statement.

Here comes the gotcha. Assuming that setting ROWCOUNT is equivalent to a TOP restriction in the SELECT statement (in SQL 2000 and lower you could not use a variable with the TOP restriction and I thought that's why the first solution was used) I replaced SET ROWCOUNT @RowCount with SELECT TOP (@RowCount). And suddenly no rows were getting selected. The difference is that if you set ROWCOUNT to 0, the next statement will not be restricted in any way. Instead, TOP 0 will return 0 rows. So, as usual, be careful with assumptions.

There are other important differences between TOP and SET ROWCOUNT. TOP accepts both numeric and percentage values. Also, SET ROWCOUNT will NOT work on UPDATE, DELETE and INSERT statements from the version of the SQL server after 2012, so it's basically obsolete. Also, the query optimizer can consider the value of expression in the TOP clauses during query optimization. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be considered in a query plan.

Update: in SQL 2012 a new options has been added to the ORDER BY clause, called OFFSET and FETCH, that finally work like the LIMIT keyword in MySQL.