Indexed View and Calculation Question

Question

Indexed View and Calculation Question

hawg

SSCertifiable

Points: 6044
More actions
November 3, 2005 at 2:54 pm

#102904

I have a question about indexed views. I had never used an index view until the last week or so when I was asked to create one. After creating it, using it and playing with it and thinking more about it we decided that was not the right decision but it still brought up a question in my mind.

The following is a scaled down example.

Let’s say I have a table called TableA with the following format:

CREATE TABLE dbo.TableA(
            Field1               int,
            Field2               varchar(50),
            Field3               varchar(20),
            Field4               int)

ALTER TABLE dbo.TableA ADD
CONSTRAINT PK_TableA PRIMARY KEY CLUSTERED(
            Field1)
ON PRIMARY

I was asked to create an indexed view like the following (assume that all of the proper settings are set, etc.):

CREATE VIEW dbo.v_TableA_View
WITH SCHEMABINDING
AS
SELECT Field1, Field2, Field3 SUM(Field4) AS My_Total, COUNT_BIG(*) AS Group_Count
FROM dbo.TableA
WHERE Field4 IS NOT NULL
GROUP BY Field1, Field2, Field3

CREATE UNIQUE CLUSTERED INDEX idx_TableA_View_CIndex
ON dbo. v_TableA_View (Field1, Field2)

Now my question?

Is the SUM function in the view calculated each time this view is queried? If so, does querying the view using the index make any difference on how the SUM is calculated versus querying the view using a non-index field?

I have not used views a lot so I am still (always?) learning. I just know there are those of you on this site that really know how the system works so I thought I’d ask.

Thanks for the insight.

hawg

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Think of an indexed view as an table where SQL Server automatically syncronizes any updates made to the underlying tables.

Maybe

If you are running either Enterprise or Developer Edition

- or -

you are running Standard Edition and specify in the from clause with (noexpand).

Then the view logic, such as a sum is not executed

otherwise, yes it is executed.

Here is a test case for Standard Edition. Look at the execution plan at the end:

set ANSI_NULLS On

set QUOTED_IDENTIFIER on

go

create view SalesSummaryByQuarter

( ShippedYear, ShippedQtr, ShippedSalesAmt, OrderDetailCount )

with SCHEMABINDING

AS

SELECT DATEPART(yyyy, Orders.ShippedDate)

, DATEPART(qq, Orders.ShippedDate)

, Sum(CONVERT(money,(OrderDetails.UnitPrice*Quantity*(1-Discount)/100))*100) AS Subtotal

,COUNT_BIG(*)

FROM dbo.Orders as Orders

INNER JOIN dbo.[Order Details] as OrderDetails

ON Orders.OrderID = OrderDetails.OrderID

WHERE Orders.ShippedDate IS NOT NULL

GROUP BY DATEPART(yyyy, Orders.ShippedDate)

, DATEPART(qq, Orders.ShippedDate)

go

create unique clustered index SalesSummaryByQuarter_P on SalesSummaryByQuarter (ShippedYear, ShippedQtr)

go

select * from SalesSummaryByQuarter with (NOEXPAND) where ShippedYear = 1997

|--Clustered Index Seek(OBJECT[northwind].[dbo].[SalesSummaryByQuarter].[SalesSummaryByQuarter_P]), SEEK[SalesSummaryByQuarter].[ShippedYear]=1997) ORDERED FORWARD)

select * from SalesSummaryByQuarter where ShippedYear = 1997

|--Stream Aggregate(GROUP BY[Expr1005]) DEFINE[Expr1007]=Count(*), [Expr1006]=SUM(Convert(Convert([Order Details].[UnitPrice]*Convert([Order Details].[Quantity]))*(1-[Order Details].[Discount])/100)*100.00), [Expr1004]=ANY([Expr1004])))

|--Sort(ORDER BY[Expr1005] ASC))

|--Filter(WHERE[Expr1004]=Convert([@1])))

|--Compute Scalar(DEFINE[Expr1004]=datepart(year, [Orders].[ShippedDate]), [Expr1005]=datepart(quarter, [Orders].[ShippedDate])))

|--Nested Loops(Inner Join, OUTER REFERENCES[Orders].[OrderID]))

|--Index Seek(OBJECT[northwind].[dbo].[Orders].[ShippedDate]), SEEK[Orders].[ShippedDate] IsNotNull) ORDERED FORWARD)

|--Clustered Index Seek(OBJECT[northwind].[dbo].[Order Details].[PK_Order_Details]), SEEK[Order Details].[OrderID]=[Orders].[OrderID]) ORDERED FORWARD)

SQL = Scarcely Qualifies as a Language

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

P.S.

The example uses the Northwind database

SQL = Scarcely Qualifies as a Language