Databases Reference
In-Depth Information
edition, and it is the only way (when using a SQL Server edition other than Enterprise) to
ask SQL Server to match an existing view.
Here's an example. Create an indexed view on
AdventureWorks
by running the
following code:
CREATE
VIEW
v_test
WITH
SCHEMABINDING
AS
SELECT
SalesOrderID
,
COUNT_BIG
(*)
as
cnt
FROM
Sales
.
SalesOrderDetail
GROUP
BY
SalesOrderID
GO
CREATE
UNIQUE
CLUSTERED
INDEX
ix_test
ON
v_test
(
SalesOrderID
);
Listing 7-27.
Next, run the following query:
SELECT
SalesOrderID
,
COUNT
(*)
FROM
Sales
.
SalesOrderDetail
GROUP
BY
SalesOrderID
Listing 7-28.
If you are using SQL Server Enterprise edition (or the Enterprise Evaluation or Developer
editions, which share the same database engine edition), you will get the following plan,
which actually matches the existing indexed view, as shown in the plan in Figure 7-17.
Figure 7-17:
Plan using an existing indexed view.