March 19, 2008 at 9:38 pm
As per Books Online --
Another benefit of creating an index on a view is that the optimizer starts using the view index in queries that do not directly name the view in the FROM clause. Existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.
I created an indexed view.
Tried queries from this view but in its execution plan it is referring to the clustered index of its base table. Why it is so ?
Also if someone can give appropriate examples of indexed views.
March 20, 2008 at 12:23 am
What edition of SQL are you using?
What's the indexed view's definition, and what does the query look like?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2008 at 12:54 am
I m working with SQL Server 2000.
An indexed view is any view with a unique clustered index ...
Attaching the query..
March 20, 2008 at 12:57 am
Which edition of SQL 2000? (Enterprise, standard, ...)
I was asing what the definition code for your indexed view is. (Create View ....)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2008 at 3:27 am
That is the Developers Edition
and the scripts are -
--creating table
CREATE TABLE [Emp] (
[EmpID] [int] NOT NULL ,
[EmpName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[salary] [decimal](9, 2) NULL ,
[EmpType] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[technicalD] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mgrid] [int] NULL ,
PRIMARY KEY CLUSTERED
(
[EmpID]
) ON [PRIMARY]
) ON [PRIMARY]
--inserted some records in it
-- creating a simple view
create view empview
as
select empname, emptype from emp where emptype='se'
select * from empview order by empname
--creating a view on emp table that will be used for indexed view
create view tempv
with schemabinding
as
select empid, empname, emptype from dbo.emp where empname like 'S%'
--creating index on the view
create unique clustered index cind
on tempv(empname)
-- the index is created successfully
-- now empview is an indexed view
[font="Courier New"]select empname, salary*.12 from emp where empname like 's%'
select empid*1,empname from tempv
select * from tempv[/font][font="Arial Black"]--all the above 3 SELECT queries using clustered index on base table (and not indexed view)
--WHY IT IS SO ?[/font]
March 20, 2008 at 5:06 am
Not sure about the second and third.
1st probably uses the base table rather than the view because the column Salary isn't in the view, hence a scan of the base table is cheaper than a scan of the indees view and lookups back to the abse table
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2008 at 5:19 am
How much data do you have in your table/view? Are your statistics up to date? If use an index hint in your query, does it speed up your query?
Regards,
Andras
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply