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
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 ... INTOfrom a join where two tables have a column of the same name, such asId.- Two expressions given the same alias in a
SELECT INTO.
How to fix it
-
Guard
ALTER TABLE ... ADDin migrations.IF COL_LENGTH(N'dbo.Orders', N'ShippedAt') IS NULL ALTER TABLE dbo.Orders ADD ShippedAt datetime2 NULL; -
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
208Invalid object name207Invalid column name4104Multi-part identifier not bound209Ambiguous column name2812Could not find stored procedure3701Cannot drop, object not found3702Cannot drop database, currently in use5030Database couldn't be exclusively locked
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.
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.