Cannot create index on view because the view is not schema bound error 1939

Question

The third part of this assignment I'm working on says, "Create and index the existing Northwind view called “dbo.Orders Qry” based on the columns OrderID and EmployeeID."

It is expected that I will get an error; however, my instructor only told us to Google it. I did, but scheme binding isn't even in this week's lesson or any others, and the things I've found are too in-depth for me to understand.

Is this an issue of me not checking a box or changing a setting somewhere?

score 25 · Answer 1 · answered Jun 28 '12 at 18:41

25

Sounds like this is describing an indexed view, you can read up on them at Microsoft's site here. Microsoft enabled this capability starting with SQL 2005.

In the text for the view definition, you'd need to add the words WITH SCHEMABINDING just after the CREATE VIEW statement, for example:

CREATE VIEW dbo.MyView
WITH SCHEMABINDING

AS

SELECT a, b, c
FROM dbo.MyTable

To add indexing, you'd add a statement to the view definition similar to this:

-- Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_MyView_ClusteredIndex
ON dbo.MyView(a, b, c)

GO

answered Jun 28 '12 at 18:41

Darth Continent

2,319
3
25
41

Cannot schema bind view 'MyView' because name 'MyTable' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. – Neru-J Mar 20 '13 at 08:48
3

Add "dbo." next to your view name. – Luis Hernández May 31 '13 at 19:27
2

the "dbo." should be next to the tables the view is reading from. – Cameron Castillo Feb 12 '15 at 09:01

score 1 · Answer 2 · answered Feb 08 '14 at 22:29

1

I was looking for exactly what was posted by Darth Continent. This worked like a charm, however, it was an entirely different situation. I believe that the answer above should at least be credited as the resolution, and if not a follow up would be great.

answered Feb 08 '14 at 22:29

Anthony Mason

165
1
12

1

This worked for me: CREATE VIEW [dbo].[PERSON_V] WITH SCHEMABINDING AS SELECT SL_LAST_NAME + '; ' + SL_FIRST_NAME AS RES_NAME, ASSIGNED_ORG, PERSON_ID, [USER_NAME] AS WINDOWS_ACCOUNT, OFFICE_BUILDING, OFFICE_ROOM, MAIL_STOP, OFFICE_PHONE, JOB_TITLE, RES_TYPE, 'TestUser@test.com' AS EMAIL_ADDR, PARENT_RSRC_ID FROM dbo.PERSON GO CREATE UNIQUE CLUSTERED INDEX IX_PERSON_ID ON dbo.PERSON_V (PERSON_ID) GO – Anthony Mason Feb 08 '14 at 22:32

Cannot create index on view because the view is not schema bound error 1939

2 Answers2

Linked