Indexed View Sql Server 2005

Question

Indexed View Sql Server 2005

IN_Sandeep

SSCertifiable

Points: 6259
More actions
April 6, 2008 at 10:53 am

#184259

Hi All,
I have a table and I created one view on it and I created Clustered Index on this view.
When i am making a Select Statement to view the data,
this view is doing table scan instead of index scan,
In this point I am really confused, then what is the used of a Indexed View??
Please Clear me on this concepts...
Code Present Below
IF OBJECT_ID('v') IS NOT NULL DROP VIEW v
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
go
CREATE TABLE t
(id int NOT NULL,
a int NOT NULL,
b float(53) NOT NULL)
GO
INSERT t VALUES(1, 1,1.0e1)
INSERT t VALUES(2, 1,1.0e2)
INSERT t VALUES(3, 2, 1.0e0)
INSERT t VALUES(4, 2, 5.0e-17)
INSERT t VALUES(5, 2, 5.0e-17)
INSERT t VALUES(6, 2, 5.0e-17)
GO
CREATE VIEW v WITH SCHEMABINDING AS
SELECT a, SUM(b) AS sum_b, COUNT_BIG(*) AS c
FROM dbo.t
GROUP BY a
GO
CREATE UNIQUE CLUSTERED INDEX idx ON v(a)
GO
Select * from v
Please help me ...
Cheers!
Sandy.
--

Viewing 9 posts - 1 through 8 (of 8 total)

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

XPSCodes-667952 SSChasing Mays Points: 603 More actions · Answer 1

We cannot use aggregate functions when using schemabinding.

--------------------------------------------------------------------------------------
Save our world, its all we have! A must watch video Pale Blue Dot[/url]

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

Sandy: not sure why your view isn't using the clustered index but the group by is not the issue. using your sample data script, here's my sqlcmd results:

2> CREATE VIEW v WITH SCHEMABINDING AS

3> SELECT a, SUM(b) AS sum_b, COUNT_BIG(*) AS c

4> FROM dbo.t

5> GROUP BY a

6> GO

1> CREATE UNIQUE CLUSTERED INDEX idx ON v(a)

2> GO

1> set showplan_text on

2> go

1> Select * from v where a = 2

2> go

StmtText

------------------------------

select * from v where a = 2

(1 rows affected)

StmtText

--------------------------------------------------------------------------------

-----------

|--Stream Aggregate(DEFINE: ([Expr1004]=SUM([staging].[dbo].[v].[sum_b]), [Expr

1005]=SUM([staging].[dbo].[v].[c]), [staging].[dbo].[t].[a]=ANY([staging].[dbo].

[v].[a])))

|--Clustered Index Seek(OBJECT: ([staging].[dbo].[v].[idx]), SEEK: ([stagin

g].[dbo].[v].[a]=(2)) ORDERED FORWARD)

(2 rows affected)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

You will never get an INDEX SEEK using SELECT *... only index scans.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

hi antonio.collins,

I have used your query too, but still same result, I mean table scan here...

|--Stream Aggregate(DEFINE:([master].[dbo].[t].[a]=ANY([master].[dbo].[t].[a])))
|--Table Scan(OBJECT:([master].[dbo].[t]), WHERE:([master].[dbo].[t].[a]=(2)))

Can u please help me on this?

hey Jeff Moden,

Can you please clear me, I am not clear about your statement and

can u give me small idea about Indexed view??

You will never get an INDEX SEEK using SELECT *... only index scans.

Can you clear me about Indexed view concepts??

Cheers!

Sandy.

--

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

You will never get an INDEX SEEK using SELECT *... only index scans.

My most sincere apologies... the comment above does NOT apply to INDEXED VIEWs.

What matters is what you have in the WHERE clause of whatever query is using the INDEXED VIEW. For example, the following WILL cause an INDEX SCAN because it needs to return ALL rows from the view and it's more efficient to do a scan...

SELECT *

FROM v

... but if you have something in the WHERE clause that is capable of using the clustered or other index, you'll get a nice fast INDEX SEEK. For example, the following will use an INDEX SEEK...

SELECT *

FROM v

WHERE A = 2

This is important when a join comes into play on the indexed column. But, depending on the query, you could still get an INDEX SCAN just like on any other derived table (which is sometimes called an "Inline View").

The real key to the speed of an INDEXED VIEW is that the aggregates are precalculated and materialized as if you had populated a temp table with the same query. That's why they don't recommend using INDEXED VIEWs on tables that are made to suffer a great many changes... everytime you change something, the INDEXED VIEW must reaggregate the answers for the affected rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

IN_Sandeep

SSCertifiable

Points: 6259

April 7, 2008 at 10:37 pm

#799534

Thanks Jeff,

Cheers!

Sandy.

--

Deepak Mohapatra SSC Enthusiast Points: 149 More actions · Answer 7

It's a very late response, but I would put my 2 cents anyway.

I totally agree to what Jeff has explained, but I guess the problem what Sandy is referring to is a little different. If the WITH (NOEXPAND) hint is used in the query then an INDEX scan will happen instead of TABLE scan.

Select * from V with (NOEXPAND)

In Sandy's case, the query processor just takes the Indexed View as a regular view and expands the view when the query is run, instead of using the Clustered Index on the view.

Deepak

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Not sure if this is relavent, but an INDEX scan on a clustered index is a table scan.