Allow for outer joins in indexed views
Currently there is a restriction for indexed views to only contain inner joins. This effectively restricts the use case to non-null fk relations. Nullable fk relations are impossible to materialize.
Upvotes: 3<=-=Feb 7 2011 2:05PM=-=>
Why not a filtered index?<=-=Feb 8 2011 10:31AM=-=>
Thanks for the feedback. We’ll consider this for a future release. The columnstore index feature coming in the Denali release provides such excellent performance that many data warehouse and data mart customers won’t need indexed views any more. So that may provide an adequate solution for you. See http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf
SQL Server Query Processing
Please don’t leave out OLTP customer… Indexed views are great for eliminating joins for mostly read data. Consider the following schema:
create table forumthreads (id, userid null, title)
create table users (id, name)
and the query:
select top 10 * from forumthreads left join users order by users.name
No single-table index can satisfy the sort order so the entire result set must be materialized and top-n sorted. It would be much more efficient to materialize that view so the first 10 rows are instantly available. This would be possible if it was an inner join. However in this example, userid is nullable. Maybe this is helpful in your decision making process.<=-=Feb 8 2011 6:16PM=-=>
That’s good feedback. Thanks!
P.S. If you need a workaround, I suppose that you could make userid not nullable, and add a “unknown” userid row to the users table. Then put “unknown” into the formthreads userid field for unknown users, and use inner join. But that’s probably not what you wanted to hear :
We’re resolving this as a duplicate. We have another item to track it.
SQL Server Query Processing
Branko Dimitrijevic commented
One scenario where outer joins in indexed views would be very beneficial is related to fulltext indexing.
Unfortunately, a fulltext index can only be created on a single table. Combining results from multiple fulltext indexes is awkward at best and extremely poorly performing at worst. Combining fulltext rank or implementing "AND NOT" logic is especially problematic.
A "multi table" fulltext index can be emulated by creating an indexed view that joins multiple tables, and then creating a fulltext index on top of it. Limiting what can be included in an indexed view therefore limits what can be fulltext indexed.
The alternative is manually maintaining a redundant table just so we can create a fulltext index on it, which is of course prone to errors (especially to concurrency errors).