Views are very useful for fetching data from multiple tables. If data is very huge, then performance of view goes very down. Microsoft has provided now ability to increase performance by indexing view. On MSDN it is explained in great details at http://msdn.microsoft.com/en-us/library/aa933148%28SQL.80%29.aspx
How to create
We will go by example we will create two tables which will have large data.
CREATE TABLE Items(
ItemID INT PRIMARY KEY,
Dsc VARCHAR(20),
)
GO
CREATE TABLE CustOrders(
OrderID BIGINT PRIMARY KEY,
CustNo BIGINT,
ItemID VARCHAR(20),
QTY INT)
GO
CREATE VIEW [dbo].[MyView]
WITH SCHEMABINDING
AS
SELECT dbo.CustOrders.OrderID, dbo.CustOrders.CustNo, dbo.Items.Dsc, dbo.CustOrders.QTY
FROM dbo.CustOrders INNER JOIN
dbo.Items ON dbo.CustOrders.ItemID = dbo.Items.ItemID
GO
Indexed views:
- Must be created the WITH SCHEMABINDING view option
- May only refer to base tables in the same database.
- If there is a GROUP BY clause, the view may not have a HAVING, CUBE, or ROLLUP.
- May not have an OUTER JOIN clause.
- May not have a UNION.
- May not have DISTINCT or TOP clauses
- May not have full-text predicates such as CONATINSTABLE
- May not have a ROWSET function such as OPENROWSET
- May not use derived tables or subqueries.
- Must be created with ANSI_NULLS ON and QUOTED_IDENTIFIER ON
