Forum Discussion

smpa01's avatar
smpa01
Community Champion
2 years ago

Creating Index on datamart SQL

How can I create SQL INDEX  on the sql views created through datamart. I consume the views and to optimize, I need to  have INDEX on them.

I keep on getting

 

cannot create index on view because it is not schema bound

 

Is there any work around this.

I currently have 20+ views in a single datamart and I need INDEX on few of them to improve performance and future proof current performance.

 

 

Need Help

6 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    2 years ago

    smpa01 Indexes on Views are only created if you use WITH SCHEMABINDING otherwise a change in the underlying table may break the materialized view

    CREATE VIEW [dbo].[test] 
WITH SCHEMABINDING 
AS

     

    • smpa01's avatar
      smpa01
      Community Champion
      2 years ago

      ThanksAntrikshSharma  Did you try this BTW? It does not work; let me know otherwise

       

       

      CREATE VIEW [model].[test] // or [dbo].[test]
WITH SCHEMABINDING 
AS 
select [Account Key] from [model].[tbl]

       

       

       

       

       

      Cannot schema bind view 'model.test'. 'model.tbl' is not schema bound.

       

       

      To clarify, I am not asking how to create index on regualr SQL views. I need to create index on the sql views genertaed through datamart.

       

      Neither the following works

      ALTER VIEW [model].[Calendar] WITH SCHEMABINDING AS

select [Fiscal Year],[Date],[Period Number],[Period Name],FiscalPeriod,[Quarter], [Custom] from [model].[Calendar]

//Cannot schema bind view 'model.Calendar' because name 'model.Calendar' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.