I’m trying to create an index on a view. I get the error that ‘the view is not schema bound.’
Soooo, I try to put it in a schema.
This is the syntax I am using -
CREATE SCHEMA AUTHORIZATION painter
CREATE VIEW large_paints AS
SELECT paint_id,color FROM paint WHERE paint_size=100
CREATE INDEX idx on large_paints (paint_id);
I get the error - Incorrect syntax near the keyword ‘INDEX’.
Any ideas?
I don’t know SQLServer but in Oracle you would create the index on the table. What’s the purpose of putting one on a view?
enipla, this worked for me…
CREATE VIEW large_paints with schemabinding AS
SELECT paint.paint_id, paint.color FROM dbo.paint WHERE paint.paint_size=100
go
CREATE unique clustered INDEX idx on large_paints (paint_id)
go
A guy on this page says that with 2000, it only works with the Enterprise or Developer Editions. Do you have one of those?
It’s complicated. This view is used by a GIS system. I relate it to a spatial table (called a layer in GIS terms. Parcel data). The View looks just like a table to the system. The GIS system requires that a field named OBJECTID (an alias I had to create in the view) is indexed or it will not perform the relate.
OBJECTID IS not the ‘join’ field. I think it’s mostly an oversite with the GIS system.
The indexed field is basically faking it out (I hope). From what I have read at ESRI, the view needs a type long unique indexed field named OBJECTID. The data in it is unimportant (as long as it’s an indexed unique long int, which I can provide from the tables ‘renameing’ a field with an alias).
Thanks, I’ll give it a ‘GO’ Monday. So you didn’t need to put it in a ‘CREATE SCHEMA…;’?
KRM Not sure, I’m not the admin on the server (well, sort of). I’m at home now, I’ll check Monday. I suspect it’s enterprise.
Not a dead end yet. Thanks everyone.
The ‘create schema’ caused an error, ie no binding. Checked online help and came up with schemabinding…