Indexed views

• StAinley

  SSC Veteran

  Points: 280

  More actions

  March 17, 2004 at 9:48 am

  #110657

  I am currently implementing some view indexing, to improve the performance of a database that we use for reporting against using Crystal Reports.  Obviously, to allow this I am using SQL Server 2000 Enterprise edn.

  I have a view that calls on an indexed view.  The query that makes up this 'top-level' view never seems to use the view indexes though.  I have other views that are simpler and for which the indexes are used.

  I know this isn't much, but if anyone can give me any pointers they'd be appreciated.

  Thanks

• Ted Pitts

  SSC Enthusiast

  Points: 124

  More actions

  March 18, 2004 at 8:27 am

  #499342

  You can use the WITH (NOEXPAND) hint to force the query to use the index.

  For example:

  SELECT * FROM dbo.MyIndexedView WITH (NOEXPAND)

  This would also allow you to use the indexed view on any version of SQL 2000 -- even MSDE.

• Coach James

  Ten Centuries

  Points: 1272

  More actions

  March 19, 2004 at 4:21 pm

  #499619

  Also, you can resort to forcing indexes, whenever the optimizer is being stubborn…

  (NOLOCK INDEX=<index_name&gt

  ---

  Regards,

  Coach James