Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script):
SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 AND 1 = (SELECT 1);
Note: the "AND 1 = (SELECT 1) is just to keep this query from being auto-parameterized, which I felt like was confusing the issue - it actually gets the same plan with or without that clause though
And here's the plan (paste the plan link):
Since there is a "top 1" there, I was surprised to see the stream aggregate operator. It doesn't seem necessary to me, since there is guaranteed to only be one row.
To test that theory, I tried out this logically equivalent query:
SELECT MAX(Revision) FROM dbo.TheOneders WHERE Id = 1 GROUP BY Id;
Here's the plan for that one (paste the plan link):
Sure enough, the group by plan is able to get by without the stream aggregate operator.
Notice that both queries read "backwards" from the end of the index and do a "top 1" to get the max revision.
What am I missing here? Is the stream aggregate actually doing work in the first query, or should it be able to be eliminated (and it's just a limitation of the optimizer that it's not)?
By the way, I realize this is not an incredibly practical problem (both queries report 0 ms of CPU and elapsed time), I'm just curious about the internals / behavior being exhibited here.
Here's the setup code I ran before running the two queries above:
DROP TABLE IF EXISTS dbo.TheOneders; GO CREATE TABLE dbo.TheOneders ( Id INT NOT NULL, Revision SMALLINT NOT NULL, Something NVARCHAR(23), CONSTRAINT PK_TheOneders PRIMARY KEY NONCLUSTERED (Id, Revision) ); GO INSERT INTO dbo.TheOneders (Id, Revision, Something) SELECT DISTINCT TOP 1000 1, m.message_id, 'Do...' FROM sys.messages m ORDER BY m.message_id OPTION (MAXDOP 1); INSERT INTO dbo.TheOneders (Id, Revision, Something) SELECT DISTINCT TOP 100 2, m.message_id, 'Do that thing you do...' FROM sys.messages m ORDER BY m.message_id OPTION (MAXDOP 1); GO