CODEWITHSUNDEEP
sqlsql-server-2008
KentZhou
KentZhou

Reputation: 25553

How to create primary key for a view in sql server?

Suppose I have two database DB1, and DB2 both under the same instance. There is a table tab2 in DB2. I created a view in DB1 to get tab2 from DB2:

CREATE VIEW [dbo].[Tab2]
AS
SELECT *
FROM  DB2.dbo.Tab2

Then I tried to create a key for tab2 in DB1:

CREATE UNIQUE CLUSTERED INDEX tab2_Key
ON dbo. tab2 (id2)

This throws the following error:

Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'Tab2' because the view is not schema bound.

How can I resolve this problem?

Upvotes: 1

Views: 2431

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Well, there are several rules for a view having an index (some cascade from rules required for schemabinding).

One of the rules is that the view can't contain SELECT *. Another is that it has to exist in the same database as the object(s) it references.

I could list out the rules for you, but they are listed in the docs here and here. And I don't think telling you the rules will accomplish much anyway.

Can you explain exactly what benefit you think a clustered index on this view would provide? Did someone tell you that an indexed view is "faster"? In this case I don't see what it will do for queries against DB2.dbo.Tab2 especially if that table already has an index on id2. This just smells wrong in several ways...

Upvotes: 4

Related Questions