SQL Server Error Messages - Msg 4511

Error Message

Server: Msg 4511, Level 16, State 1, Procedure <View or Function Name>, Line 1
Create View or Function failed because no column name was specified for column <Column Number>

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.

Views are created using the CREATE VIEW statement. The basic syntax of the CREATE VIEW is as follows:

CREATE VIEW [ <schema_name>. ] <view_name> [ ( column [, …n ] ) ]
[ WITH { [ENCRYPTION], [SCHEMABINDING], [VIEW_METADATA] } ]
AS <select_statement>
[ WITH CHECK OPTION ]

Just like in a table, one of the restrictions when creating a view is that the names of the columns must be specified; otherwise this error message will be raised. Here’s a script that shows a view definition that generates this error message:

CREATE TABLE [dbo].[Employee] (
    [EmployeeID]        INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [FirstName]         VARCHAR(50),
    [LastName]          VARCHAR(50)
)
GO

CREATE VIEW [dbo].[EmployeeView]
AS
SELECT [EmployeeID], [FirstName], [LastName],
       [LastName] + ', ' + [FirstName]
FROM [dbo].[Employee]
GO

Since there’s no column name assigned to the concatenation of the [LastName] and the [FirstName] in the SELECT statement of the view, the following error message is raised by the CREATE VIEW statement:

Msg 4511, Level 16, State 1, Procedure EmployeeView, Line 3
Create View or Function failed because no column name was specified for column 4.

Solution / Work Around

This error can easily be avoided by making sure that all columns returned by a view definition have assigned column names. Here’s an updated version of the view earlier that avoids this error message:

CREATE VIEW [dbo].[EmployeeView]
AS
SELECT [EmployeeID], [FirstName], [LastName],
       [LastName] + ', ' + [FirstName] AS [FullName]
FROM [dbo].[Employee]
GO

As an alternative, the second way of creating a view can be used wherein the column names used by the view are included in the CREATE VIEW statement, as can be seen in the following:

CREATE VIEW [dbo].[EmployeeView] 
( [EmployeeID], [FirstName], [LastName], [FullName] )
AS
SELECT [EmployeeID], [FirstName], [LastName],
       [LastName] + ', ' + [FirstName]
FROM [dbo].[Employee]
GO