强制受信任的外键SQL Server允许在视图中进行连接剔除

I'm trying to create a star schema view of fact to it's dimension.

我正在尝试创建一个事实的星型模式视图到它的维度。

eg.

例如。

if the view is

如果视图是

Select  
    _fact.A, _Dim.B
from 
    _fact
inner join 
    _dim on _Fact.dim_sk = _dim.Dim_sk

and I query

我查询

Select _Fact.A 
from _view

It will ignore the join in the _dim .

它将忽略_dim中的连接。

One way of doing this is to create trusted foreign keys to the dimension. However this means I have to create and enable check constraints which will slow down my inserts.

执行此操作的一种方法是为维度创建受信任的外键。但是，这意味着我必须创建并启用检查约束，这将减慢我的插入速度。

https://www.brentozar.com/archive/2015/05/do-foreign-keys-matter-for-insert-speed/

Is there a better way to allow join culling in a view?

有没有更好的方法允许在视图中进行连接剔除？
Is there a way to force SQL server to mark foreign keys as trusted?

有没有办法强制SQL服务器将外键标记为可信？

e.g. something along the lines of

例如一些东西

update sys.foreign_keys
set is_not_trusted = 0

1 个解决方案

#1

First, you really may want enforced FKs in your schema. You can disable them and check them at the end of your ETL if you want, and they are useful.

首先，您可能希望在架构中强制执行FK。如果需要，您可以禁用它们并在ETL的末尾检查它们，它们很有用。

At some scale, however, you want to leave the FKs as NOCHECK for performance.

但是，在某种程度上，您希望将FK保留为NOCHECK以提高性能。

So the alternative is to use a LEFT JOIN, and then so long as the DIM table has a unique index on the join column, SQL knows that the join can't change the cardinality of the result, and will eliminate the join if don't reference the dimension columns. eg:

所以替代方法是使用LEFT JOIN，然后只要DIM表在连接列上有唯一索引，SQL就知道连接不能改变结果的基数，并且如果不知道就会消除连接。 t引用维度列。例如：

    use tempdb

    go

    drop table if exists _fact 
    drop table if exists _dim
    go
    create table _dim(Dim_sk int primary key, B int)

    create table _fact( A int, dim_sk int references _dim)

    go

    create or alter view _view
    as
    Select  
        _fact.A, _Dim.B
    from 
        _fact
    left join 
        _dim on _Fact.dim_sk = _dim.Dim_sk

    go
    set statistics io on
    go
    Select A 
    from _view
    go
    set statistics io off

outputs

输出

Table '_fact'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

#1