Not Quite To the Top

After the past few blog posts, I’m sure you’re like “Sherpa! How many more things can we learn about the TOP Operator?”

My friends, the answer is “Still more!”

Today, let’s go over an option that can be used in conjunction with TOP. That option is SET ROWCOUNT (Number);

This option has the potential to override the TOP operator if the number used with ROWCOUNT is lower than the number used with TOP. Let’s see this in action, shall we?

First, we run a query that we’ve run before just to see what the data looks like:

SELECT TOP (50) *
FROM dbo.NirvanaPerformances
ORDER BY TRY_CONVERT(DATE, ConcertDate) DESC

We get back the top 50 rows of Nirvana’s performances. No big deal, right?

Next, we run the same query in conjunction with SET ROWCOUNT 40 – notice a smaller number than 50.

SET ROWCOUNT 40;

SELECT TOP (50) *
FROM dbo.NirvanaPerformances
ORDER BY TRY_CONVERT(DATE, ConcertDate) DESC

And we get 40 rows returned instead of 50!

If you were to run SET ROWCOUNT 200, you will get the 50 rows that you asked for with TOP.

1. If you use SET ROWCOUNT to anything, be sure to SET ROWCOUNT 0 or you will be limiting everything to that number. Yes, that includes UPDATEs, DELETEs, and INSERTs.
2. In a future version of SQL Server, the UPDATEs, DELETEs, and INSERTs will not be affected by the use of SET ROWCOUNT. If you are relying on that functionality now, you should be changing your code to use the TOP operator just in case this happens on the next version of SQL Server.
3. Using SET ROWCOUNT will be slower than using TOP because the SQL Server query optimizer cannot use any value used outside of the query to help processing. For example, if you “SELECT TOP (50)”, the query optimizer can stop processing after 50 records are found. The “SET ROWCOUNT 50;” on the other hand, is outside the scope of what the query optimizer can work with.

Please be sure to remember the three items above if you do decide to use SET ROWCOUNT. They can definitely help you when writing queries.

With that, my friends, it is time for me to leave you. Hopefully, you learned about the SET ROWCOUNT option and now understand how it differs from the TOP operator. Until next time!