I'm finding that, on an indexed view with appropriate indexes, MAX(date) is performing an entire index scan followed by a stream aggregate whereas TOP (1) date is optimally using the index and only scanning a single row. For large numbers of rows this is leading to serious performance issues. I've included some code to demonstrate the issue below but would be interested to know if others can explain why this behaviour is occuring (it does not occur on a table with similar index) and whether it is a bug in SQL Server's optimiser (I've tested on both 2008 SP2 and on R2, and both show the same issues).
CREATE TABLE dbo.TableWithDate
(
id INT IDENTITY(1,1) PRIMARY KEY,
theDate DATE NOT NULL
);
CREATE NONCLUSTERED INDEX [ix_date] ON dbo.TableWithDate([theDate] DESC);
INSERT INTO dbo.TableWithDate(theDate) VALUES('1 MAR 2010'),('1 MAR 2010'), ('3 JUN 2008');
-- Test 1: max vs top(1) on the table. They give same optimal plan (scan one row from the index, since index is in order)
SELECT TOP(1) theDate FROM dbo.TableWithDate ORDER BY theDate DESC;
SELECT MAX(theDate) FROM dbo.TableWithDate;
CREATE TABLE dbo.TheJoinTable
(
identId INT IDENTITY(1,1) PRIMARY KEY,
foreignId INT NOT NULL,
someValue INT NOT NULL
);
CREATE NONCLUSTERED INDEX [ix_foreignValue] ON dbo.TheJoinTable([foreignId] ASC);
INSERT INTO dbo.TheJoinTable(foreignId,someValue) VALUES (1,10),(1,20),(1,30),(2,5),(3,6),(3,10);
GO
CREATE VIEW dbo.TheTablesJoined
WITH SCHEMABINDING
AS
SELECT T2.identId, T1.id, T1.theDate, T2.someValue
FROM dbo.TableWithDate AS T1
INNER JOIN dbo.TheJoinTable AS T2 ON T2.foreignId=T1.id
GO
-- Notice the different plans: the TOP one does a scan of 1 row from each and joins
-- The max one does a scan of the entire index and then does seek operations for each item (less efficient)
SELECT TOP(1) theDate FROM dbo.TheTablesJoined ORDER BY theDate DESC;
SELECT MAX(theDate) FROM dbo.TheTablesJoined;
-- But what about if we put an index on the view? Does that make a difference?
CREATE UNIQUE CLUSTERED INDEX [ix_clust1] ON dbo.TheTablesJoined([identId] ASC);
CREATE NONCLUSTERED INDEX [ix_dateDesc] ON dbo.TheTablesJoined ([theDate] DESC);
-- No!!!! We are still scanning the entire index (look at the actual number of rows) in the MAX case.
SELECT TOP(1) theDate FROM dbo.TheTablesJoined ORDER BY theDate DESC;
SELECT MAX(theDate) FROM dbo.TheTablesJoined;
