Can't create indexed view against table with masked columns I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view.

create unique clustered index [IX_Name]
on dbo.vw_ViewName(SomeUniqueId)


I get this error:


  The index on view 'dbo.vw_ViewName' cannot be created
  because the view is referencing table 'dbo.TableName' with masked
  columns.


In another environment, where masking is not enabled, the index is created successfully.

I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index.

Here is some SQL to reproduce the issue:

drop view if exists dbo.vw_Aggregate
drop table if exists dbo.MainTable, dbo.SecondaryTable
go

create table dbo.MainTable
(
    MainTableId uniqueidentifier primary key,
    SomeExternalId uniqueidentifier,
    SecondaryTableId uniqueidentifier
)
go

create table dbo.SecondaryTable
(
    SecondaryTableId uniqueidentifier primary key,
    CreatedOn datetime,
    Amount decimal(19, 8),
    -- the below column produces error,
    -- if commented out - there is no error
    [Description] nvarchar(max) masked with (function = 'default()'),
    Dummy int
)
go

create view dbo.vw_Aggregate with schemabinding
as
    select AggregateId = m.MainTableId,
           m.SomeExternalId,
           s.CreatedOn,
           s.Amount
    from dbo.MainTable m
    inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId
go

create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)]
on dbo.vw_Aggregate(AggregateId)
go
As far as I know, this is not supported, and you can only use tables in this case.

As found in following article which is using Row level security which is not the same as Dynamic Data masking, but it might be linked to each other.

https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ by Aaron Bertrand:

Indexed Views

Row-Level Security is not compatible with tables that have indexed views. If you try to create an indexed view against a table with a security policy, you will get the following error:


  Msg 33266, Level 16 The index on the view 'view' cannot be created
  because the view is  referencing table 'table' that is referenced by a
  security policy.


And no, you can't chicken-and-egg this to work around it; if a table is referenced by an indexed view, a security policy cannot be applied:


  Msg 33265, Level 16 The security policy 'policy' cannot have a
  predicate on table 'table' because this table is referenced by the
  indexed view 'view'.


Note: You may come across the same types of problems if trying to apply a security policy to a table that is reference by a partitioned view.

As Dynamic Data Masking &amp; Row Level Security were released in the same release maybe the limitations are linked to each other.

The Error

The error also clearly says that it is not allowed to have an index on a view linked to a table with a masked column. In the documentation however nothing can be found about it.

So my guess is that it is just not supported.