Index on Views ...

I know that we can create indexes on views (Indexed views) in sql 2k. I am
trying to understand what would be the reason for creating a index on a view
.
Anyway a view is a virtual table and if there is an index on those table
won't it be enough? Hope somebody could make me understand this.
Regards
PradeepHi
I think you missed the concept of INDEXED VIEWS
http://www.sql-server-performance.com/indexed_views.asp
In addition please take a look at Steve Kass's scipt to ensure that using
an indexe view SQL Server creates more efficient execution plan
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
"SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>I know that we can create indexes on views (Indexed views) in sql 2k. I am
> trying to understand what would be the reason for creating a index on a
> view.
> Anyway a view is a virtual table and if there is an index on those table
> won't it be enough? Hope somebody could make me understand this.
> Regards
> Pradeep|||Thanks for ur response Uri. I would go through that link.
Regards
Pradeep
"Uri Dimant" wrote:

> Hi
> I think you missed the concept of INDEXED VIEWS
> http://www.sql-server-performance.com/indexed_views.asp
>
> In addition please take a look at Steve Kass's scipt to ensure that using
> an indexe view SQL Server creates more efficient execution plan
> create table T (
> i int,
> filler char(1000) default 'abc'
> )
> go
> create view T_count with schemabinding as
> select
> cast(i as bit) as val,
> count_big(*) T_count
> from dbo.T group by cast(i as bit)
> go
> create unique clustered index T_count_uci on T_count(val)
> go
> insert into T(i)
> select OrderID
> from Northwind..[Order Details]
> go
> set statistics io on
> select count(*) from T
> go
> select sum(T_count) from T_count with (noexpand)
> go
> set statistics io off
> -- uses an efficient query plan on the materialized view
> go
> drop view T_count
> drop table T
>
>
> "SqlBeginner" <SqlBeginner@.discussions.microsoft.com> wrote in message
> news:F47FC536-3817-4DCE-A1F5-8E1C077ACDCF@.microsoft.com...
>
>

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
The following link has some useful information on improving performance using indexed views:
http://msdn.microsoft.com/library/de...exedviews1.asp
However, to be specific to your scenario, you might consider running a workload through Index Tuning Wizard to see whether it suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.
|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:

> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA
|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
(Please reply only to the newsgroup)
|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

index on view

If an application uses a lot of views and the performance
is slow, should we create indexes on views? What kind of
indexes to create?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
The following link has some useful information on improving performance =using indexed views:
http://msdn.microsoft.com/library/default.asp?url=3D/library/en-us/dnsql2=
k/html/indexedviews1.asp
However, to be specific to your scenario, you might consider running a =workload through Index Tuning Wizard to see whether it suggests that you =create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" <kqd02@.yahoo.com> wrote in message =news:143df01c444cd$f6355010$a301280a@.phx.gbl...
If an application uses a lot of views and the performance is slow, should we create indexes on views? What kind of indexes to create?
Thanks.
--=_NextPart_000_0008_01C44498.B5850D10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The following link has some useful information on =improving performance using indexed views:
http://msdn.microsoft.com/library/defau=lt.asp?url=3D/library/en-us/dnsql2k/html/indexedviews1.asp
However, to be specific to your scenario, you might =consider running a workload through Index Tuning Wizard to see whether it =suggests that you create any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Julia" wrote in message news:143df01c444cd$=f6355010$a301280a@.phx.gbl...If an application uses a lot of views and the performance is slow, =should we create indexes on views? What kind of indexes to create?Thanks.

---

--=_NextPart_000_0008_01C44498.B5850D10--|||You may consider indexed view, some people call it materialized view
too. Beware that there're alot limitations on indexed view, for detail,
read BOL.
You may also want to investigate how those views are constructed, are
they nested views? views joining another view? Based on my experience,
joining differnet views are bad idea, it may be easy to program, but
performance really sucks.
If that's not the case, run those views inside query analyzer to see if
there are any table scans, then create index accordingly.
Eric
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
Eric Li
SQL DBA
MCDBA|||If performance is low, then you should start by adding the appropriate
indexes to the base tables that are used in the view. SQL-Server will
automatically take these into consideration.
If that doesn't help (enough), and you are running Enterprise Edition of
SQL-Server 2000, you could consider indexed views.
Hope this helps,
Gert-Jan
Julia wrote:
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.
--
(Please reply only to the newsgroup)|||Hi
Just to add to the other posts...
You may also want to consider if the view is being used appropriately!!!
e.g. It is not a great idea of using a view that joins half a dozen tables
when you only want data from a single base table.
John
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.|||Be very careful about creating indexed views. The cost of maintenance can be
very high... So exhaust all other possibilities prior to choosing indexed
views as a solution.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julia" <kqd02@.yahoo.com> wrote in message
news:143df01c444cd$f6355010$a301280a@.phx.gbl...
> If an application uses a lot of views and the performance
> is slow, should we create indexes on views? What kind of
> indexes to create?
> Thanks.

Index in view

I 've 5 tables not indexed(must).
Now i create 5 views for these with indexing.
creating index only on views are possible, if so it can increase the
performance od query. Can u give me the soln ?Read about indexed views in Books Online. Note that only Enterprise Edition
will use such indexes
automatically. I would reconsider why you cannot create indexes on the base
tables. Why is that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JJFreds" <JJFreds@.discussions.microsoft.com> wrote in message
news:9A6FA327-D8F1-4A85-99F4-E521EEA400D8@.microsoft.com...
>I 've 5 tables not indexed(must).
> Now i create 5 views for these with indexing.
> creating index only on views are possible, if so it can increase the
> performance od query. Can u give me the soln ?

INDEX and VIEWS

Hi all,
Let say that I have a table Customer
CREATE TABLE [dbo].[Customer] (
[CustomerId] [int] NOT NULL ,
[CustomerName] [nvarchar] (50),
[CustomerAge] [int] NOT NULL
) ON [PRIMARY]
GO
Let say I have an index on CustomerAge.
If I have a view defined as:
CREATE VIEW dbo.VIEWCustomer
AS
SELECT dbo.Customer.*
FROM dbo.Customer
and then if I execute the following SQL statement:
select * from VIEWCustomer where CustomerAge = 25
Will that statement use the index of the table Customer (on the field
CustomerAge) or will it not (because no index can be defined on a view)?
In other words, if a select on a view is using a WHERE clause for which
there is an index defined for the table.field defined in the view, will it
be used or not?
Best regards,
Francois MalgreveYou can check this yourself by examining the execution plan in Query
Analyzer. The indexes certainly can be used when referencing a view in
just the same way as they are with tables.
David Portas
SQL Server MVP
--