July 23, 2009 at 3:15 pm
I have created an indexed view as given below
CREATE VIEW [dbo].[test] WITH SCHEMABINDING AS
SELECT COUNT_BIG(*) AS count, A.dest as dest
FROM dbo.or_gr A JOIN dbo.or_st B ON B.or_id = A.or_id
JOIN dbo.or AS C ON C.o_id = A.c_o_id
JOIN dbo.o_t AS D ON D.o_t = C.o_t
JOIN dbo.o_s AS E ON E.s = C.s
JOIN dbo.t_i_f_c AS F ON F.t_i_f= C.t_i_f
WHERE A.dest IN ('AB','AU','CD','CE','CGI','CI','ET','IS','KO','ML','NI','SU','TR','UBS','WF')
AND A.o_s_id IN (2)
AND C.s IN ('1','2','3','4','5','6')
AND C.o_t IN ('1','2','3','4','5','6','7','8','9','P')
AND C.t_i_f IN (0,1,2,3,4,5,6,7)
GROUP BY A.dest
I have also created a unique clustered index on the 'dest' column.
When I fire a query
SELECT * FROM dbo.test (OR) SELECT count FROM dbo.test
The optimizer uses execution plan for the under lying query used to form the view instead of using the Index on the view itself.
Also, I expect the optimizer to use the index when I fire a query with same select columns and where clause. This does not happen either.
When I add 'WITH (NOEXPAND)' hint, The view usues its index to fetch the results
Can any body throw any ideas around this behaviour and let me know if I am missing something to get the right results.
Thanks much for help.
July 23, 2009 at 3:25 pm
What version of SQL are you using? Standard, Enterprise, etc?
On versions other than Enterprise (and Dev) you can create indexed views but the index will NEVER be used unless the engine is Enterprise (Or Dev)..
CEWII
July 23, 2009 at 3:28 pm
I am using SQL Server 2005 Dev edition(SP3) and situation is same on SQL server 2008 dev edition too
July 23, 2009 at 4:15 pm
Elliott W (7/23/2009)
What version of SQL are you using? Standard, Enterprise, etc?On versions other than Enterprise (and Dev) you can create indexed views but the index will NEVER be used unless the engine is Enterprise (Or Dev)..
CEWII
Not quite true. From BOL:
Indexed views can be created in any edition of SQL Server 2005. In SQL Server 2005 Enterprise Edition, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.
July 25, 2009 at 6:39 am
I stand corrected, however, that used to be true in SQL 2000. I admit I didn't re-research it, perhaps I should have..
CEWII
July 26, 2009 at 5:46 pm
It looks like indexed views do use their index without NOEXPAND option on Developer edition too.. Please try below test script.
The execution plans for last two statements shows the usage of indexed view.
But
for some reason my actually view (in my original post) is not exhibiting this behaviour.
/**** This script is to use similar query conditions as in the original post***/
--creating tables
create table T1(c1 int, c2 int)
go
create table T2 (c3 int, c2 int)
go
--cretaing view
create view v1 with schemabinding
as
select T2.c3, count_big(*)as count_ from dbo.T1 T1 join dbo.T2 T2 on T1.c1 = T2.c3 where T2.c3 =1 group by T2.c3
go
-- cretaing index on view
create unique clustered index idx_v1_c1 on v1 (c3)
select * from v1
select T2.c3, count_big(*)as count_ from dbo.T1 T1 join dbo.T2 T2 on T1.c1 = T2.c3 where T2.c3 =1 group by T2.c3
July 26, 2009 at 8:45 pm
Hima Bindu Nagisetty (7/26/2009)
It looks like indexed views do use their index without NOEXPAND option on Developer edition too.. Please try below test script.The execution plans for last two statements shows the usage of indexed view.
But
for some reason my actually view (in my original post) is not exhibiting this behaviour.
/**** This script is to use similar query conditions as in the original post***/
--creating tables
create table T1(c1 int, c2 int)
go
create table T2 (c3 int, c2 int)
go
--cretaing view
create view v1 with schemabinding
as
select T2.c3, count_big(*)as count_ from dbo.T1 T1 join dbo.T2 T2 on T1.c1 = T2.c3 where T2.c3 =1 group by T2.c3
go
-- cretaing index on view
create unique clustered index idx_v1_c1 on v1 (c3)
select * from v1
select T2.c3, count_big(*)as count_ from dbo.T1 T1 join dbo.T2 T2 on T1.c1 = T2.c3 where T2.c3 =1 group by T2.c3
Well, the Developer Edition is the Enterprise Edition with different licensing, as it can't (or shouldn't) be used in a production environment.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply