// SQL Server error reference

SQL Server error 2705: Column names must be unique

A CREATE TABLE, ALTER TABLE ADD or SELECT INTO would create two columns with the same name in one table.

Error number
2705
Severity
16
Category
Names and objects
Error message
Column names in each table must be unique. Column name '%.*ls' in table '%.*ls' is specified more than once.

What it means

Within a table every column name must be unique, compared using the database's collation (so on a case-insensitive database Name and NAME clash). Error 2705 names the duplicate column.

Common causes

  • Adding a column that already exists, for example re-running a migration.
  • SELECT ... INTO from a join where two tables have a column of the same name, such as Id.
  • Two expressions given the same alias in a SELECT INTO.

How to fix it

  1. Guard ALTER TABLE ... ADD in migrations.

    IF COL_LENGTH(N'dbo.Orders', N'ShippedAt') IS NULL
        ALTER TABLE dbo.Orders ADD ShippedAt datetime2 NULL;
  2. Alias clashing columns in SELECT INTO.

    SELECT o.Id AS OrderId, c.Id AS CustomerId
    INTO #orders
    FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.Id = o.CustomerId;

Related errors

More names and objects errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.