Creating Index on datamart SQL

[smpa01]

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.

[AntrikshSharma]

@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]

Thanks@AntrikshSharma  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.

[AntrikshSharma]

@smpa01 Is this datamart created in Power BI service or an external server?

[smpa01]

@AntrikshSharma  created on Power BI service.

[AntrikshSharma]

@smpa01 using SSMS I noticed that the 'tables' in datamart are actually Views so even if you try to use SCHEMABINDING in the new view you somehow first need to to SCHEMABIND the original view. And for some reason I am not even able to create View while in the same workspace I can create Views in the Lakehouse as well as a Warehouse, so I am not sure if creation of view is actually allowed right now in datamarts?

[smpa01]

@AntrikshSharma I have Fabric but Lakehouse is not approved by IT yet, hence Fabric DW is not much of use to me. Till then Datamart is my best bet and all my datasources are ON-PREM.

Datamart is pretty solid (though pending any support Power Automate) due to the avaialbilty of SQL endpoints. However, the data ingested through PQ gets converted into SQL view without giving me any option to create index. I mean I can always create a table variable and / temp table and assign INDEX but that stays within the scope of query. Hence, I was looking for a better solution to have index on the table/views itself to be utilized in downstream queries seamlessly.

While I have you and it seems like you are already engineering data through it. Do you know what are the options Fabric has for data coming from SQL server configured to ON-PREM gateway. To elaborate, do notebook and Data Factory supports ON Prem yet? I know for a fact dataflow GEN2 supports it. If you have any knowledge and share with me would be great.

I will oepn the thread for others to share their views ; specfically someone from MS.