Force trusted foreign key SQL Server to allow join culling in view

Question

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 .

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/

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

2. Is there a way to force SQL server to mark foreign keys as trusted?

e.g. something along the lines of

update sys.foreign_keys
set is_not_trusted = 0

Answer 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.

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

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:

    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.