As I understand it, SQL Stored Procedures can cache the execution plan, thereby improving performance. Are there anything like indexed views that can achieve the same?
I tried to research the benefits of indexed views in comparison to stored procedures. I would like to implement views but am getting push back from DBAs who insist that stored procedures are the only way to go.
>Solution :
This article might help you. It states warnings to consider with indexed views and discusses the execution plans in indexed views.
"Creating indexed views differs from creating normal views in that using the SCHEMA BINDING hint is not optional. This means that you will not be able to apply structure changes on the tables that may affect the indexed view unless you alter or drop that indexed view first. In addition, you need to specify two parts name of these tables including the schema with the table name in the view definition. Also, any user-defined function that is referenced by the created indexed view should be created using WITH SCHEMABINDING hint.
Once the Indexed view is created, its data will be stored in your database the same as any other clustered index, so the storage space for the view’s clustered index should be taken into consideration. Having the indexed view’s clustered index stored in the database, with its own statistics created to optimize the cardinality estimation, different from the underlying tables’ statistics, the SQL engine will not waste the time substituting the source tables’ definition in the main query, and it will read directly from the view’s clustered index."
https://www.sqlshack.com/sql-server-indexed-views/