Indexing Views

Question

Indexing Views

Elizabeth Crepeau

SSC Enthusiast

Points: 111
More actions
July 18, 2002 at 8:54 am

#158435

Can you create an index on a view using SQL Server 2000 Standard edition? If so, how?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

cafemar1 SSCrazy Points: 2405 More actions · Answer 1

Yes you can. Instead of giving you a big explanation of this matter (BOL explains better than I can), please refer to BOL and search for a section called "Creating an Indexed View" This will give you the info you need. Good luck

Elizabeth Crepeau SSC Enthusiast Points: 111 More actions · Answer 2

I previously checked out that BOL topic and found the following:

Note: You can create indexed views only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

We are using the Standard Edition.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Unfortunately we run the two that can here. But you are right Standard does not support indexed views.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Elizabeth Crepeau SSC Enthusiast Points: 111 More actions · Answer 4

Seems you CAN create indexes on views using SQL Server Standard edition. Check out the following:

USE Northwind

GO

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON

GO

--Create view.

CREATE VIEW V1

WITH SCHEMABINDING

AS

SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT

FROM dbo.[Order Details] od, dbo.Orders o

WHERE od.OrderID=o.OrderID

GROUP BY OrderDate, ProductID

GO

--Create index on the view.

CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)

GO

--thank you Traci Catches!!

lslmustang SSC Veteran Points: 264 More actions · Answer 5

This is in BOL:

Note Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used.

Best of luck,

Linda

EricsON Old Hand Points: 375 More actions · Answer 6

I recommend that you update your BOL to the latest available. I too recall seeing that Indexed Views were only an Enterprise/Developer feature, but the latest BOL (as quoted above) says differently. Simple add the optimizer hint NOEXPAND and all should be happy.

I think the only thing that ISN'T supported is Federated Views.

Clint SSC Veteran Points: 207 More actions · Answer 7

I'm a rank novice, so the problem I have is that after I've created views in SQL 2000, sometimes Enterprise Manager will let me Manage Indexes..., and other times it won't, and I haven't figured out the pattern yet. What do I have to do to get indexed views everywhere?

philcart SSC-Forever Points: 47794 More actions · Answer 8

quote:
I'm a rank novice, so the problem I have is that after I've created views in SQL 2000, sometimes Enterprise Manager will let me Manage Indexes..., and other times it won't, and I haven't figured out the pattern yet. What do I have to do to get indexed views everywhere?

Check out "Creating an Indexed View" and "SET Options That Affect Results" in Books Online.

The SET options are particularly important.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

EricsON Old Hand Points: 375 More actions · Answer 9

quote:
I'm a rank novice, so the problem I have is that after I've created views in SQL 2000, sometimes Enterprise Manager will let me Manage Indexes..., and other times it won't, and I haven't figured out the pattern yet. What do I have to do to get indexed views everywhere?

Lemme save you a little bit of frustration with indexed views. I was trying to implement a concept whereby I built up a local or distributed partitioned view using a UNION ALL. Then I tried to index it and kept getting the error message 1936 "Cannot index the view X. It contains one or more disallowed constructs.". Finally I read the BOL properly and it states that views cannot be indexed if they use the UNION ALL construct.

Hope it helps you or anyone else.

kirbles SSC-Addicted Points: 464 More actions · Answer 10

I use indexed views in Enterprise, Standard, Developer, and Desktop editions of SQL Server. To ensure that the compiler takes advantage of the indexed view, use the (NOEXPAND) view hint. After I learned that, I've had no problems.