SET ROWCOUNT overrides TOP?

While I was going through an implemented set of codes, I noticed that ROWCOUNT is heavily used (of course, it is an old implementations) and not only that, TOP is with newly added codes. There is an interesting behavior when these two are combined, which was one of the issues I was troubleshooting. Let me explain with an example.

First of all, remember, there is a Behavior Change with ROWCOUNT in SQL Server future release. There will be NO effect on INSERT, DELETE and UPDATE statement with ROWCOUNT in SQL Server future release. Therefore avoid ROWCOUNT with these action statements, instead use TOP.

Have a look on below query. It sets ROWCOUNT to 100, instructing that stop processing the query after 100 rows are returned. However, TOP is used with SELECT, limiting records to 10 records. Because of that we get only 10 records. This proves that TOP overrides ROWCOUNT.

USE AdventureWorks2016CTP3;
GO

SET ROWCOUNT 100;
SELECT TOP (10) *
FROM Sales.SalesOrderHeader;

Now let's change the query bit. Let's instruct SQL Server to process only 10 records using ROWCOUNT and have TOP 100 with SELECT statement.

USE AdventureWorks2016CTP3;
GO

SET ROWCOUNT 10;
SELECT TOP (100) *
FROM Sales.SalesOrderHeader;

What we expect is 100 records as we know that TOP overrides ROWCOUNT but it returns only 10.

What is the reason? This is how it works when ROWCOUNT and TOP are combined. ROWCOUNT overrides TOP keyword in the SELECT only if the set value for ROWCOUNT is smaller that value set with TOP. This is something you need to remember. Best is, try to use only TOP without using ROWCOUNT.