Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 sp_helpindex on a view

Author  Topic 

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2004-02-23 : 12:24:22
Hi,
I have a view which has a column as a primary key. When I do sp_helpindex view_name, I get a message saying that the object doesn't have indexes. Is this normal?
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 14:26:36
[code]
USE Northwind
GO

sp_help Orders
GO
sp_helpindex Orders
GO
CREATE VIEW v_Orders AS SELECT * FROM Orders
GO
sp_help v_Orders
GO
sp_helpindex v_Orders
GO

--CREATE INDEX IX1 ON v_Orders (ShipVia, ShippedDate)
--Server: Msg 1939, Level 16, State 1, Line 1
--Cannot create index on view 'v_Orders' because the view is not schema bound.
--
-- What does that mean?
GO

DROP VIEW v_Orders
GO

[/code]

Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-23 : 15:20:56
Sarat,

A view has an index only if you created one. Indexes are usually only created on tables. You can put them on views. They are then called indexed views.


Brett,

Schema bound has to do with all of the requirements for indexed views. Here is one of them:

"The view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables."


Tara
Go to Top of Page

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2004-02-24 : 15:58:17
Sorry for the confusion! I was talking about an indexed view. What I missed was I built the view via PSoft desginer but didn't check the create index check box along with it. So when I did sp_helpindex in QA, i didn't see anything!!
Thank You both!!
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**
Go to Top of Page
   

- Advertisement -