If you’re getting an error that reads something like “Column names in each table must be unique. Column name ‘c1’ in table ‘t1’ is specified more than once” in SQL Server, it appears that you’re trying to create a table with duplicate column names, or you’re trying to add a column to a table that already has a column of that name.

Column names must be unique within each table.

To fix this issue, make sure each column is listed only once in each table.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE t1 (
    c1 int,
    c1 int
);

Output:

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'c1' in table 't1' is specified more than once.

It can also happen when we try to add a column to an existing table:

-- Create the table
CREATE TABLE t2 (
    c1 int,
    c2 int
);

-- Add a duplicate column
ALTER TABLE t2 ADD c2 int;

Output:

Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'c2' in table 't2' is specified more than once.

As the error messages tell us, column names must be unique within each table.

Solution

To fix this issue, make sure each column name is unique within its table.

Here’s an example of fixing the first issue:

CREATE TABLE t1 (
    c1 int,
    c2 int
);

Output:

Commands completed successfully.

Success.

Now let’s change the second example to one that works:

-- Create the table
CREATE TABLE t2 (
    c1 int,
    c2 int
);

-- Add a duplicate column
ALTER TABLE t2 ADD c3 int;

Output:

Commands completed successfully.

Fixed.

These are overly simplistic examples, and in practice, the column names would normally be more descriptive of their function/utility, which may make trying to think of a new name a bit hard. Either way, it must be done. And this is a good opportunity to review your database schema to see whether it still meets your needs for the growth of the database.