|
|
Error Message
Server: Msg 1054, Level 15, State 1, Line 1
Syntax '*' is not allowed in schema-bound objects.
Causes
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.
|
One of the requirements or restrictions when using the SCHEMABINDING clause when creating or altering a view definition is that the “*” syntax, which specifies that all columns from all tables and views in the FROM clause are included in the view definition, is not allowed.
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]
WITH SCHEMABINDING
AS
SELECT * FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
Msg 1054, Level 15, State 6, Procedure Customer_NY, Line 4
Syntax '*' is not allowed in schema-bound objects.
Solution / Work Around:
As the error message suggests, the “*” is not allowed in schema-bound objects such as the view being created earlier which uses the SCHEMABINDING option. This error message is easily resolved by simply listing all the columns from the base table that the view needs to return:
CREATE VIEW [dbo].[Customer_NY]
WITH SCHEMABINDING
AS
SELECT [CustomerID], [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
One of the reasons for the need of the SCHEMABINDING option when creating a view is for the creation of indexed views. One of the restrictions of creating an indexed view is that the view must be created with the WITH SCHEMABINDING option. If indexes are not needed to be created on a view, then there’s no need to include the WITH SCHEMABINDING option when creating a view and therefore the “*” syntax can be use for the view definition:
CREATE VIEW [dbo].[Customer_NY]
AS
SELECT * FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO
|