SQL Views and Performance

After my last post of SQL, Case sensitivity, and views, Brian Kotek brought up an excellent point, bringing up performance concerns.

It turns out that views are very handy, but not very optimized for performance. Tables are generally indexed. SQL has the ability to index data of particular columns so it doesn't have to deep-scan the data each time you query that table. All primary keys are indexed, but you can create as many additional indexes as you want (when and why for another post).
If you use a view, your columns will not be indexed automatically. With SQL 2000, Microsoft introduced View Indexes; SQL Server View Indexes are dynamic and changes to the data in the base tables are automatically reflected in the indexed view. Your columns will be automatically indexded only if your view complies with certain pre-requisites:

• 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

CREATE VIEW OrderDetailsXSB   WITH SCHEMABINDING 
AS
SELECT OD.OrderID, OD.ProductID, P.ProductName , OD.UnitPrice
     , OD.Quantity, OD.Discount
FROM dbo.Products P
    INNER JOIN dbo.[Order Details] OD

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO