SQL Server Indexed View and TOP
Outer Apply
You're using OUTER APPLY
, but with a where clause that would reject NULL
values.
It's converted to an inner join without the TOP (1)
:
SELECT
U.Id,
A.Reputation,
A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
SELECT
v.*
FROM dbo.vwHighReputation AS v
WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
I've formatted your code a little bit, and added an ORDER BY
to validate results across queries. No offense.
Outer Apply + TOP (1)
When you use the TOP (1)
, the join is of the LEFT OUTER
variety:
SELECT
U.Id,
A.Reputation,
A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
SELECT TOP (1)
v.*
FROM dbo.vwHighReputation AS v
WHERE v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
The TOP (1)
inside the OUTER APPLY
apparently makes the optimizer unable to apply the same transformation to an inner join, even with a redundant predicate:
SELECT
U.Id,
A.Reputation,
A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
SELECT TOP (1)
v.*
FROM dbo.vwHighReputation AS v
WHERE v.Id = U.Id
AND v.DisplayName LIKE 'J%'
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
Note the residual predicates to evaluate if the Id
and DisplayName
columns are NULL
.
This isn't just a TOP (1)
issue either -- you can substitute any values up to the big int max (9223372036854775807) and see the same plan.
It will also happen if you skip the view entirely.
SELECT
U.Id,
A.Reputation,
A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
SELECT TOP (1)
v.Id,
v.DisplayName,
v.Reputation
FROM dbo.Users AS v
WHERE v.Reputation > 10000
AND v.Id = U.Id
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id
OPTION(EXPAND VIEWS);
A Rewrite
One way to get the same effect as TOP (1)
without the various optimizer side effects of TOP
is to use ROW_NUMBER
SELECT
U.Id,
A.Reputation,
A.DisplayName
FROM dbo.Users AS U
OUTER APPLY
(
SELECT
v.*
FROM
(
SELECT
v.*,
ROW_NUMBER() OVER
(
PARTITION BY
v.Id
ORDER BY
v.Id
) AS n
FROM dbo.vwHighReputation AS v
) AS v
WHERE v.Id = U.Id
AND v.n = 1
) AS A
WHERE A.DisplayName LIKE 'J%'
ORDER BY U.Id;
Which will get you the original plan: