Materialized view or Index view in sql server

SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When we call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly. Materialized view is a special type of view that persistent data that means the query used to define a materialized view creates a special table in the disk memory.

Read view: View in sql server

https://bageshkumarbagi-msbi.blogspot.com/2016/06/view-in-sql-server.html

 When a materialized view is created, the results of the associated query are computed and stored in a table in the database. This table is updated periodically based on a schedule or triggered by certain events, such as changes to the underlying data. Queries that reference the materialized view can then retrieve the pre-computed results directly from the table, rather than executing the original query each time.

Materialized views can be particularly useful for data warehousing and business intelligence applications, where complex queries are often run against large data sets. By pre-computing the results of these queries and storing them in a materialized view, query response times can be significantly improved.

Syntax of materialized view

Create view <view_name> with schemabinding as <Select statement>

IF Object_id('dbo.vw_Product') IS NOT NULL   DROP VIEW vw_product; CREATE VIEW vw_product WITH schemabinding AS   SELECT a.productid,          a.NAME,          a.productnumber,          b.salesorderid,          b.salesorderdetailid,          b.carriertrackingnumber   FROM   production.product a          INNER JOIN sales.salesorderdetail b                  ON a.productid = b.productid go CREATE UNIQUE CLUSTERED INDEX ix_pk   ON vw_product(productid, salesorderdetailid)

View and index are created. See the data

Index view must have one UNIQUE CLUSTERED index. We can created multiple non cluster index on the view.

Now we are creating the non-cluster index on this view.

CREATE NONCLUSTERED INDEX ix_vw_product_productid   ON vw_product (productid)   include (NAME, productnumber, salesorderid,  salesorderdetailid, carriertrackingnumber)

Index created successfully.