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?

Answer 1

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

Answer 2

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.