[SOLVED] What is the most efficient way to check if more than x rows exist?

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

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.

Solution

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;

One other method, somewhat more complex too understand, is to use an OFFSET...FETCH clause. You must add an ORDER BY though

SELECT 1
FROM [Meta].BannerViews 
WHERE CustomerId = '6cc639d6-eb07-49bb-9f8d-e3d0d6f063ae'
ORDER BY (SELECT NULL)
OFFSET @X ROWS FETCH NEXT 1 ROW ONLY;

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.