Making decision between Count() & Top queries in terms of performance

We are using SQL Server and this is a performance related question.

We have a relatively big table and intend to perform an action in case of having more than 1 record with the same ID. Consider the simple queries below:

select top 2 [TenantID] from [dbo].[tblTenant] where TenantID = 1

vs

select COUNT ([TenantID]) from [dbo].[tblTenant] where TenantID = 1

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs. But in the second query, operation is done over all the records in the table.

Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

I would guess in the first query, SQL Server stops scanning the entire table after finding the first possible two TenantIDs.

Correct, it will stop after finding two matching rows. But it doesn't necessarily scan the whole table. If there is a suitable index, on (TenantID), it will only need an index seek and a sequential scan on the index - and stop at the second row, whether it is a match or not. The index guarantees that the rows with same TenantID will be one after the other in the index structure.


But in the second query, operation is done over all the records in the table.

Yes again. With the same note, if there is an index, it only has to do an index seek and a scan of the index, until it finds the first row with a different (than 1) TeanantID. If there is no index, it will have to do a full scan on the table.


Is that a correct assumption? I mean, could we say the first query is better in terms of performance?

Yes, that is a correct assumption. The first query will be (slightly or more) efficient than the second. The difference will probably be negligible though in most cases. Reading 2, 3 or 10 values from an index will not make any noticeable difference. If however, there are many rows with the same TenantID value, say tens of thousands or millions, then you'll start noticing.

To summarize, if you need to find whether there are N or more rows that match a condition, I suggest you always use the TOP (N) method, and then count:

... 
WHERE (SELECT COUNT(*) FROM
        (select top (2) * from [dbo].[tblTenant] where TenantID = 1) AS t
      ) = 2 ;

StackExchange Database Administrators Q#161192, answer score: 2