I have a Very Large/slow table (35gb data, 70gb indexes) on an Azure SQL MI that’s roughly this:

create table a (id int identity, mydate datetime, fielda varchar(10), fieldb money, fieldc char(50), fieldd datetime)
create index ncidx__a__mydate on a (mydate)

I want to get a list of which hours have 0 rows, though which hours have any rows is close enough (bang it against a numbers table and you can get a list). I was trying to think of the bare minimum needed.

I could do something like this, but would need 30*24 of them for each month. Seems like something a function with a cross-apply could do.

SELECT * FROM (SELECT '20240703 1am test' res) a WHERE EXISTS (SELECT * FROM dbo.mytable WHERE mydate >='20240701 01:00:00' AND mydate <'20240701 02:00:00')

I came up with this (based off an Itzik Ben-Gan query), which… works, and is good enough for me for now. (3 minutes for a month, 200m rows, 34gb data, 70gb of indexes including the one you see. It does 715992 logical reads).

SELECT orderday, orderhour, MIN(mydate) AS mindate
FROM mytable
CROSS APPLY (VALUES(day(mydate), datepart(HOUR,mydate)))
               AS D(orderday, orderhour)
WHERE mydate >='20240501' AND mydate <'20240601'
GROUP BY orderday, orderhour

But is there a faster way? I don’t need a min date, I just need to know if there are ANY rows in there. It seems like it could be almost instant, but the logic of doing that is beyond me. Thanks.