SQL 用什么替换视图中的左连接以便我可以拥有索引视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6476933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What to replace left join in a view so i can have an indexed view?
提问by Noble_Bright_Life
I have normalized tables in a database and to denormalize it, I created a view out of two tables. When I tried to create a clustered index on the view, it wouldn't let me, as the view was created with a left outer join. I used a left join because I want the null values to show up in the resulting view, much like how it was suggested in this earlier post.
我已经规范化了数据库中的表并对其进行了非规范化，我从两个表中创建了一个视图。当我试图在视图上创建聚集索引时，它不会让我，因为视图是用左外连接创建的。我使用了左连接，因为我希望空值显示在结果视图中，就像在之前的帖子中建议的那样。
Question on join where one column one side is null
The table structure and relationship is very much similar to what was described in the above link.
表结构和关系与上述链接中描述的非常相似。
I seemed to hit a wall here as I couldn't convert my left join into an inner join, as that would exclude all records with null values on any of the joined columns. My questions are:
我似乎在这里碰壁了，因为我无法将左联接转换为内联接，因为这会排除任何联接列上具有空值的所有记录。我的问题是：
- Why is indexing not allowed on outer or self joins?
- Are there any performance hits on this kind of un-indexed view?
- Anyone knows any workaround to this problem?
- 为什么在外部或自连接上不允许索引？
- 这种未编入索引的视图是否会影响性能？
- 任何人都知道这个问题的任何解决方法？
I've just finished a SQL Server course yesterday so don't know how to proceed. Would appreciate any comments. Cheers.
我昨天刚刚完成了 SQL Server 课程，所以不知道如何继续。将不胜感激任何意见。干杯。
采纳答案by Magnus
There is a "workaround" herethat involves check for NULLin the join and having a NULLrepresentation value in the table
有一个“解决办法”这里涉及检查NULL在连接并具有NULL在表中表示值
NULL value
空值
INSERT INTO Father (Father_id, Father_name) values(-255,'No father')
The join
加入
JOIN [dbo].[son] s on isnull(s.father_id, -255) = f.father_id
回答by cocogorilla
Here is an alternative. You want a materialized view of A not containing B. That isn't directly available... so instead, materialize two views. One of all A's and one of only A's with B's. Then, get only A's not having B's by taking A except B. This can be done efficiently:
这是一个替代方案。你想要一个不包含 B 的 A 的物化视图。那不是直接可用的......所以相反，物化两个视图。所有 A 之一和仅有的 A 与 B 之一。然后，通过取除 B 之外的 A，只得到 A 没有 B。这可以有效地完成：
Create two materialized views (mA and mAB) (edit: mA could just be the base table). mA lacks the join between A and B (thus containing all A's period [and therefore containing those records WITHOUT matches in B]). mAB joins between A and B (thus containing only A's with B's [and therefore excluding those records WITHOUT matches in B]).
创建两个物化视图（mA 和 mAB）（编辑：mA 可能只是基表）。mA 缺少 A 和 B 之间的连接（因此包含所有 A 的句点 [因此包含那些在 B 中没有匹配项的记录]）。mAB 在 A 和 B 之间连接（因此只包含 A 和 B [因此排除那些在 B 中没有匹配项的记录]）。
To get all A's without matches in B, mask out those that match:
要获得 B 中没有匹配项的所有 A，请屏蔽那些匹配项：
with ids as (
select matchId from mA with (index (pk_matchid), noexpand)
except
select matchId from mAB with (index (pk_matchid), noexpand)
)
select * from mA a join ids b on a.matchId = b.matchId;
This should yield a left anti semi join against both your clustered indexes to get the ids and a clustered index seek to get the data out of mA you are looking for.
这应该对您的聚集索引产生左反半连接以获取 id 和聚集索引寻求从您正在寻找的 mA 中获取数据。
Essentially what you are running into is the basic rule that SQL is much better at dealing with data that IS there than data that ISN'T. By materializing two sources, you gain some compelling set based options. You have to weigh the cost of these views against those gains yourself.
从本质上讲，您遇到的是基本规则，即 SQL 在处理存在的数据方面比不存在的数据要好得多。通过具体化两个来源，您可以获得一些引人注目的基于集合的选项。您必须自己权衡这些观点的成本与这些收益。
回答by Klaus
I don't think there is a good workaround. What you can do about this is to create a real table from the view and set indexes on that. This can be done by a stored procedure that is called regularly when data is updated.
我认为没有好的解决方法。您可以做的是从视图创建一个真实的表并在其上设置索引。这可以通过在数据更新时定期调用的存储过程来完成。
Select *
into <REAL_TABLE>
From <VIEW>
create CLUSTERED index <INDEX_THE_FIELD> on <REAL_TABLE>(<THE_FIELD>)
But this is only a noteworthy approach if data isn't updated every few seconds.
但如果数据不是每隔几秒更新一次，这只是一种值得注意的方法。
回答by Anon
Logically you are making two separate queries. 'A LEFT JOIN B' is just shorthand for '(A JOIN B) UNION A'
从逻辑上讲，您正在进行两个单独的查询。'A LEFT JOIN B' 只是 '(A JOIN B) UNION A' 的简写
The first query is table A inner joined to table B. This gets an indexed view, since this is where all the heavy lifting is done.
第一个查询是表 A 内部连接到表 B。这将获得索引视图，因为这是完成所有繁重工作的地方。
The second query is just table A where any of the join columns are null. Make a view that produces the same output columns as the first query and pads them with nulls.
第二个查询只是表 A，其中任何连接列都为空。创建一个视图，该视图生成与第一个查询相同的输出列，并用空值填充它们。
Just union the two results before returning them. No need for a workaround.
只需在返回两个结果之前合并它们。不需要解决方法。
回答by Damien_The_Unbeliever
I'll work on an answer to 1, but for now:
我将研究 1 的答案，但现在：
[2]. The view will be no more nor less performant than the equivalent query on the udnerlying tables. All the usual advice applies about having covering indexes, preferably an index on the joined columns, etc.
[2]。该视图的性能不会高于或低于对 udnerlying 表的等效查询。所有通常的建议都适用于覆盖索引，最好是连接列上的索引等。
[3]. There's no real workaround. Most of the restrictions on indexed views exist for very good reasons, once you dig into them.
[3]。没有真正的解决方法。对索引视图的大多数限制都是有充分理由的，一旦你深入研究它们。
I'd just create the view, generally, and do no more, unless there was a specific performance problem.
通常，我只会创建视图，并且不会再执行其他操作，除非存在特定的性能问题。
I'll try to add an answer for 1 once I've reconstructed it in my own mind.
一旦我在自己的脑海中重建它，我会尝试为 1 添加答案。
