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 ;