1) You do it by following books online's instructions.

2) As for why, an indexed view is litterally created and laid down on disk,
and as such can be direct access to perhaps a very limited set of data as
opposed to possibly reading large numbers of tables/IOs to develop the
resultset at query time.  As an extreme example, suppose you have an
aggregate view that hits a billion row fact table joined to a million row
dimension table.  The indexed view could be a single 8k page IO to give you
the result (which was precomputed at create time) as opposed to having to
perform perhaps hundreds of thousands or more IOs and join those two tables
together to get you the result.

An index on a view materializes the view, so that it does not have to be
done at run time.  I do this in a few systems to pre-aggregate roll-ups.
For example, I might have an orders table like this (let's assume OrderDate
is just the whole date with no time component):

CREATE TABLE dbo.Orders
(
   OrderID INT,
   OrderDate SMALLDATETIME
);

If I am frequently counting the orders by day, I may create a view like
this:

CREATE VIEW dbo.OrdersByday
WITH SCHEMABINDING
AS
   SELECT
       OrderDate,
       COUNT_BIG(*)
   FROM
       dbo.Orders
   GROUP BY
       OrderDate
GO

Now if I want that view to work faster, I can force it to materialize by
creating an index:

CREATE UNIQUE CLUSTERED INDEX d ON dbo.OrdersByDay(OrderDate);

This actually forces the result of the view to be persisted to disk.  What
this means internally is that when the Orders table is updated / deleted /
inserted, so is the view.  So you pay for the faster queries in terms of
maintenance costs of the index(es).  You can change the queries to reference
the view directly or, in some cases, at least with enterprise edition, the
optimizer can make the leap for you, and utilize the view even though you
specify the base table.

Much better description here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

On 7/19/08 8:19 AM, in article #AGf0mZ6IHA.3816@TK2MSFTNGP03.phx.gbl, "Jay"
<noreply@here.orhere> wrote:

Your are right that a regular view (non-indexed) is basically just an
encapsulated SQL statement.  However, when you create a clustered index on a
view, the actual view results are materialized and requires additional space
in the database just like a regular table.  Furthermore, the indexed view is
automatically updated when data in the underlying table(s) change.
Illustration scripe below.

SET NOCOUNT ON

--create base table
CREATE TABLE dbo.OrderDetail
(
OrderID int NOT NULL,
OrderDetailID int IDENTITY(1,1) NOT NULL,
OrderQty smallint NOT NULL,
ProductID int NOT NULL,
UnitPrice money NOT NULL,
UnitPriceDiscount money NOT NULL,
LineTotal AS COALESCE(UnitPrice * (1.0 - UnitPriceDiscount) * OrderQty,
0.0),
 CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY
CLUSTERED
(
 OrderID,
 OrderDetailID
)
)
GO

--create view
CREATE VIEW ProductOrderSummary
WITH SCHEMABINDING
AS
SELECT
ProductID,
SUM(OrderQty) AS OrderQty,
SUM(OrderQty * UnitPrice) AS GrossPriceTotal,
SUM(LineTotal) AS DiscountedPriceTotal,
COUNT_BIG(*) AS OrderLineCount
FROM dbo.OrderDetail
GROUP BY
ProductID
GO

--index the view
CREATE UNIQUE CLUSTERED INDEX ProductOrderSummary_ProductID ON
dbo.ProductOrderSummary(ProductID)
GO

--insert data into base table
INSERT INTO dbo.OrderDetail
(
OrderID,
OrderQty,
ProductID,
UnitPrice,
UnitPriceDiscount
)
SELECT 1, 10, 1, 5.00, 0
UNION ALL SELECT 1, 10, 2, 4.00, 0
UNION ALL SELECT 2, 15, 1, 5.00, 0.05
UNION ALL SELECT 2, 5, 2, 4.00, 0.00
UNION ALL SELECT 2, 20, 3, 1.00, 0.00
UNION ALL SELECT 3, 15, 2, 4.00, 0.00
UNION ALL SELECT 3, 1, 3, 1.00, 0.00

--show data from table and view
SELECT * FROM dbo.OrderDetail
SELECT * FROM dbo.ProductOrderSummary

--note count actual rows stored in database
SELECT OBJECT_NAME(object_id), rows
FROM sys.partitions
WHERE object_id IN(
   OBJECT_ID('dbo.OrderDetail'),
   OBJECT_ID('dbo.ProductOrderSummary'))