What is the most efficient way to check if more than x rows exist?-CodePudding

I need to check if there are more than x rows in a large database. I came up with this:

SELECT 1 FROM (SELECT NULL AS X FROM [Meta].BannerViews 
WHERE CustomerId='6cc639d6-eb07-49bb-9f8d-e3d0d6f063ae') AS T0 HAVING COUNT(*) > X

But is this the most efficient way to do this? Let's stay COUNT(*) is 100_000 and X = 2.

CodePudding user response：

The fastest way in this particular instance would be to add TOP(@X 1) to the inner query, this means it would bail out when more than that number is reached

SELECT 1
FROM (
    SELECT TOP (@X   1) NULL AS X FROM [Meta].BannerViews 
    WHERE CustomerId = '6cc639d6-eb07-49bb-9f8d-e3d0d6f063ae'
) AS T0
HAVING COUNT(*) > @X;

If you want to count the whole table, not just a subset, and don't mind an approximate answer, then use one of the solutions to this question:
Fastest way to count exact number of rows in a very large table?
Those will not work here as you are counting a subset of the table.