Sql-server – Indexed view referencing objects on two different schemas

clustered-indexmaterialized-viewsql serversql-server-2016

When I try to create/alter a view to create an index like this

CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation
   ON sfdc.vSalPopulation (ID);

I get the following error message

Msg 1938, Level 16, State 1, Line 40 Index cannot be created on view
'vSalPopulation' because the underlying object 'YR_TRM_SBTRM_TABLE'
has a different owner.

When I check the tables I see that the tables are owned by different schemas

exec sp_tables 'dbo.YR_TRM_SBTRM_TABLE'
exec sp_tables 'vSalPopulation'

TABLE_QUALIFIER     TABLE_OWNER       TABLE_NAME              TABLE_TYPE    REMARKS
MyDB                dbo               YR_TRM_SBTRM_TABLE      TABLE         NULL 
MyDB                sfdc              vSalPopulation          VIEW          NULL

The documentation on indexed views states that you cannot have an indexed view that references two different databases.

The view must be created by using the WITH SCHEMABINDING option.
The view must reference only base tables that are in the same database as
the view.
The view cannot reference other views. … etc

However, I have the same database but two different schemas. Maybe the problem is actually the third required. While I am not referencing other views, there are functions. Maybe I misunderstand the error message. Permissions? So, generally, is it possible to have an indexed view that references objects from two different schemas?

A simplified definition of the view that gives me the same error looks like this

ALTER VIEW sfdc.vSalPopulation
   WITH SCHEMABINDING 
AS
SELECT DISTINCT
    ID
FROM dbo.CAN
INNER JOIN dbo.YR_TRM_SBTRM_TABLE YTS ON CAN.YR_CDE = YTS.YR_CDE
WHERE YTS.SBTRM_END_DTE > GETDATE()

Best Answer

I think I found the answer here. Basically, grant authorization on my second schema to dbo:

ALTER AUTHORIZATION ON SCHEMA::sfdc TO dbo

So, it really has to do with ownership/authorization rather than the schema itself. See: https://www.sqlteam.com/articles/understanding-the-difference-between-owners-and-schemas-in-sql-server

Related Solutions

Sql-server – Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() – Two part name needed

The answer is to add sys. in front of the hierarchyId:

sys.hierarchyid::GetRoot()

Sql-server – DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.

The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when it does not. Thinking that NULLs are excluded, it is able to match the filtered nonclustered index on the Users table that filters on ImageObjectID IS NOT NULL.

By producing a plan that uses this filtered index, it ensures that rows with NULL in ImageObjectID are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.

The view definition is:

SELECT
    dbo.Universities.ID AS Universities_ID, 
    dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
    ON dbo.Universities.AdminUserID = dbo.Users.ID

The ON clause equality comparison between AdminUserID and ID rejects NULLs in those columns, but not from the ImageObjectID column.

Part of the DBCC generated query is:

SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND) 
WHERE NOT EXISTS
( 
    SELECT 1 
    FROM   [dbo].[mv_Universities_Users_ID] tInner
    WHERE 
    (
        (
            (
                [tInner].[Universities_ID] = [tOuter].[Universities_ID]
            ) 
            OR 
            (
                [tInner].[Universities_ID] IS NULL
                AND [tOuter].[Universities_ID] IS NULL
            )
        )
        AND
        (
            (
                [tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
            ) 
            OR 
            (
                [tInner].[Users_ImageObjectID] IS NULL 
                AND [tOuter].[Users_ImageObjectID] IS NULL
            )
        )
    )
)
OPTION (EXPAND VIEWS);

This is generic code that compares values in a NULL-aware fashion. It is certainly verbose, but the logic is fine.

The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:

Erroneous plan

The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN hint to force that plan shape with the same query text submitted from a user database connection.

The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.

Best Answer

Related Solutions

Sql-server – Visual Studio DAC with Schemabound View containing hierarchyid::GetRoot() – Two part name needed

Sql-server – DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

Related Question