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 requirement? 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()