|
|
Error Message
Server: Msg 1939, Level 16, State 1, Line 1
Cannot create index on view '<View Name>' because
the view is not schema bound.
Causes
A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from one or more tables referenced in the query defining the view and are produced dynamically when the view is referenced.
|
Creating indexes on views are allowed as long as the first index created on the view is a unique clustered index. After the unique clustered index has been created, additional non-clustered indexes can be created. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
One of the restrictions of creating an indexed view is that the view must be created with the WITH SCHEMABINDING option. The SCHEMABINDING option when creating a view binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. If an index is created on a view that was not defined with the WITH SCHEMABINDING option, this error message is raised by SQL Server.
To illustrate, here’s a script that will show how this error message can be encountered:
CREATE TABLE [dbo].[Customer] (
[CustomerID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[FirstName] VARCHAR(50) NOT NULL,
[LastName] VARCHAR(50) NOT NULL,
[Address] VARCHAR(100) NOT NULL,
[City] VARCHAR(50) NOT NULL,
[State] CHAR(2) NOT NULL,
[ZIP] VARCHAR(10) NOT NULL
)
GO
CREATE VIEW [dbo].[Customer_NY]
AS
SELECT * FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
CREATE UNIQUE CLUSTERED INDEX [IX_Customer_NY] ON [dbo].[Customer_NY] ( [CustomerID] )
GO
CREATE INDEX [IX_Customer_NY_Name] ON [dbo].[Customer_NY] ( [LastName], [FirstName] )
GO
Msg 1939, Level 16, State 1, Line 2
Cannot create index on view 'Customer_NY' because the view is not schema bound.
Since the view ([dbo].[Customer_NY]) was not defined with the WITH SCHEMABINDING option, this error message is raised when an index is created over the view.
Solution / Work Around:
As mentioned earlier, before an index can be created on a view, the view must be defined with the WITH SCHEMABINDING option. Using the view already defined earlier, the following ALTER VIEW statement can be issued to include the WITH SCHEMABINDING option:
ALTER VIEW [dbo].[Customer_NY]
WITH SCHEMABINDING
AS
SELECT * FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
However, issuing this ALTER VIEW statement will generate the following error message:
Msg 1054, Level 15, State 6, Procedure Customer_NY, Line 4
Syntax '*' is not allowed in schema-bound objects.
Given that the “*” syntax is not allowed in schema-bound objects, all that needs to be done is to expand the “*” syntax by listing all the columns from the base table that needs to be returned by the view:
ALTER VIEW [dbo].[Customer_NY]
WITH SCHEMABINDING
AS
SELECT [CustomerID], [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
|