INDEXED VIEW IN SQL SERVER and Materialized Views in Oracle

 INDEXED VIEW IN SQL SERVER

 

Indexed View is similar to Materialized Views. Indexed Views used in SQL server and Materialized Views used in Oracle.

Indexed Views

The Result Set is stored in the Database. Query Performance can be dramatically enhanced using Indexed Views. Create an Indexed View by implementing a UNIQUE CLUSTERED index on the view. The results of the view are stored in the leaf-level pages of the clustered index.

An Iindexed View automatically reflects modifications made to the data in the base tables after the index is created, the same way an index created on a base table does. As modifications are made to the data in the base tables, the data modifications are also reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.

Guidelines for Creating Indexed Views

The SQL Server Query Optimizer automatically determines whether a given query will benefit from using an Index View.

Create Indexed Views when:

1. The performance gain of improved speed in retrieving results outweighs the increased maintenance cost.

2. The underlying data is infrequently updated.

3. Queries perform a significant number of joins and aggregations that either process many rows or are performed frequently by many users.

Restrictions on Creating Indexed Views

Consider the following guidelines:

1. The first index that you create on the view must be a UNIQUE CLUSTRERD index

2. You must create the view with the SCHEMABINDING option.

3. The view can reference base tables, but it cannot reference other views.

4. You must use two-part names to reference tables.

For example: -

 

CREATE VIEW VW_EXAMPLE

WITH SCHEMABINDING

AS

SELECT SUM(UnitPrice * Quantity * (1.00-Discount)) As Rev,

OrderDate, ProductID, COUNT_BIG() AS COUNT

FROM dbo.[Order_details] od

INNER JOIN dbo.Orders O

WHERE od.OrderID = O.OrderID

GROUP BY OrderDate, ProductID

----- Create UNIQUE CLUSTERED index on the view

CREATE UNIQUE CLUSTERED INDEX INDEX_VW_EXAMPLE
  ON VW_EXAMPLE (OrderDate, ProductID)

WITH SCHEMABINDING

We bind the object to the schema of all the underlying tables and views. This means that the underlying tables and views cannot be modified in a way that would affect the definition of the schema-bound object.

NOTE:

 

For schema binding, SQL server requires a more specific and safer SELECT statement. There are a couple of restrictions, which we must remember:

1. We must use two-part names, when referencing tables, views or functions (SchemaName.ObjectName).

2. We cannot use SELECT *, so we must choose specific column names. Otherwise, we will get the following error:

For example:

CREATE VIEW dbo.vwName

WITH SCHEMABINDING

AS

SELECT * FROM dbo.Employee

 

It shows you below error: -

Msg 1054, Level 15, State 6, Procedure vwName, Line x

Syntax ‘*’ is not allowed in schema-bound objects.

 

Materialized Views

Materialized view in SQL is also a logical structure which is stored physically on the disc. Like a view in Materialized views in SQL we are using simple select statement to create it. You should have created materialized views privileges to create a Materialized view. Definition of Materialized views (called as MV) has been stored in databases.  

 

 

Materialized views in SQL Syntax and Examples:

“Materialized views are also known as snapshots.”

Snapshots acts like a physical table because data from snapshots are storing in to physical memory. Snapshot retrieves data very fast. So, for performance tuning Snapshots are used. Following is the syntax of materialized view:

Create materialized view View_Name

 

Build [Immediate/Deffered]

 

Refresh [Fast/Complete/Force]

 

on [Commit/Demand]

 

as Select ..........;

 

Using above syntax, you can create materialized views. The Syntax includes some different optional fields:

1.Build Immediate: Means materialized views(mv) created immediately.

2.Build Deferred: Means materialized views(mv) created after one refresh.

3.Refresh on commit:

This option committed the data in materialized views in SQL immediately after data inserted and committed in table. This option is known as incremental refresh option. View is not fully refreshed with this option

4.Refresh on Demand:

Using this option, you can add the condition for refreshing data in materialized views.