Views and indexes

5 Jan 2006 4:41 PM

Tumurbaatar S.

Do I need to create indexes on a view to speed up ordering and searching,
if tables already have needed indexes? For example, if I specify ordering
on some column of a view (or searching on this column) and a source table
already has an index on this column, I think, the SQL2K uses this index
to optimize a query.

5 Jan 2006 5:11 PM

marcmc

The optimiser should use the base tables indices when you query the view,
however some performance gains have been made on large views by creating an
indexed view. There are, however, a number of constraints and some
limitations with indexed views to consider. To see if an indexed view suits
your requirements I suggest you reead about these issues in the books on line.

Show quote

"Tumurbaatar S." wrote:

> Do I need to create indexes on a view to speed up ordering and searching,
> if tables already have needed indexes? For example, if I specify ordering
> on some column of a view (or searching on this column) and a source table
> already has an index on this column, I think, the SQL2K uses this index
> to optimize a query.
>
>
>

5 Jan 2006 5:12 PM

Dan Guzman

The optimizer will consider indexes on the underlying base tables so you
don't need to separately create view indexes. Indexes on views can improve
performance in some situations (especially aggregations) since data are
materialized in the view index. However, view indexes aren't normally
implemented for OLTP applications.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Tumurbaatar S." <spam_tumur@magicnet.mn> wrote in message
news:eSlKdbhEGHA.2712@TK2MSFTNGP10.phx.gbl...
> Do I need to create indexes on a view to speed up ordering and searching,
> if tables already have needed indexes? For example, if I specify ordering
> on some column of a view (or searching on this column) and a source table
> already has an index on this column, I think, the SQL2K uses this index
> to optimize a query.
>
>