Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : Beals Conjecture - A Search for Counterexamples Using SQL Server
Error Messages
Home > SQL Server Error Messages > Msg 4506 - Column names in each view or function must be unique. Column name '<Column Name>' in view or function '<View or Function Name>' is specified more than once.
SQL Server Error Messages - Msg 4506 - Column names in each view or function must be unique. Column name '<Column Name>' in view or function '<View or Function Name>' is specified more than once.

Error Message

Server: Msg 4506, Level 16, State 1, Procedure <View or
Function Name>, Line 1
Column names in each view or function must be unique.
Column name '<Column Name>' in view or function '<View 
or Function Name>' is specified more than once.

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 unique; otherwise this error message will be raised. Here’s a simple script that illustrates how this error message can be encountered:

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

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

CREATE VIEW [dbo].[EmployeeManager]
AS
SELECT *
FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Manager] [Mgr]
  ON [Emp].[ManagerID] = [Mgr].[ManagerID]
GO
Msg 4506, Level 16, State 1, Procedure EmployeeManager, Line 4
Column names in each view or function must be unique.
Column name 'ManagerID' in view or function 'EmployeeManager' is specified more than once.

This error message is raised because both tables have the [ManagerID] as a column and both columns are being included in the view as separate columns. The error only raised the first column that was encountered that violated the unique column rule. As can be seen from the table definition, aside from the [ManagerID], both tables also have the [FirstName] and [LastName] columns.

Solution / Work Around:

This error can easily be avoided by making sure that the names of the columns included in the view are unique. One way to avoid this is by not using the “*” wild card in the SELECT statement particularly when the view references more than 1 table.

Here’s an updated version of the CREATE VIEW statement earlier which lists the columns to be included in the view and making sure that they are all unique:

CREATE VIEW [dbo].[EmployeeManager]
AS
SELECT [Emp].[EmployeeID], [Emp].[FirstName], [Emp].[LastName],
       [Mgr].[ManagerID], [Mgr].[FirstName] AS [ManagerFirstName],
       [Mgr].[LastName] AS [ManagerLastName]
FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Manager] [Mgr]
  ON [Emp].[ManagerID] = [Mgr].[ManagerID]
GO

A couple of things were done with the view object creation that avoided this error. First is that it returned the [ManagerID] column only once. Since this column was used in the INNER JOIN statement, the value of this column on both tables will be the same. Second, since both tables contain [FirstName] and [LastName] columns, one of these columns were assigned a different name in the view. In this particular case, the manager’s first name and last name were given column aliases of [ManagerFirstName] and [ManagerLastName], respectively, thus avoiding this error.

Related Articles :