用什么替换视图中的左连接以便我可以拥有索引视图? [英] What to replace left join in a view so i can have an indexed view?

问题描述

我对数据库中的表进行了规范化并对其进行了非规范化，我从两个表中创建了一个视图.当我试图在视图上创建聚集索引时，它不会让我，因为视图是用左外连接创建的.我使用了左连接，因为我希望空值显示在结果视图中，就像之前这篇文章中建议的那样.

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.

关于一列一侧是连接的问题空

表结构和关系与上述链接中描述的非常相似.

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:

1. 为什么不允许在外连接或自连接上建立索引?
2. 这种未编入索引的视图是否会影响性能?
3. 有人知道解决此问题的方法吗?

我昨天刚刚完成了 SQL Server 课程，所以不知道如何继续.将不胜感激任何意见.干杯.

I've just finished a SQL Server course yesterday so don't know how to proceed. Would appreciate any comments. Cheers.

推荐答案

有一个解决方法" 此处 涉及检查连接中的 NULL 并在表中具有 NULL 表示值

There is a "workaround" here that involves check for NULL in the join and having a NULL representation value in the table

空值

INSERT INTO Father (Father_id, Father_name) values(-255,'No father')

加入

JOIN [dbo].[son] s on isnull(s.father_id, -255) = f.father_id

这篇关于用什么替换视图中的左连接以便我可以拥有索引视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！