If you’re getting SQL Server error 1909 that reads something like “Cannot use duplicate column names in index. Column name ‘FirstName’ listed more than once“, it appears that you’re trying to create an index, but you’re listing the same column multiple times.

Each column can only be listed once in the index definition.

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

Example of Error

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

CREATE NONCLUSTERED INDEX IX_MyIndex 
ON Employees(FirstName,LastName,FirstName);

Output:

Msg 1909, Level 16, State 1, Line 1
Cannot use duplicate column names in index. Column name 'FirstName' listed more than once.

Here, I listed FirstName twice in the index definition.

We’ll get the same error even if the second column reference is in the INCLUDE argument:

CREATE NONCLUSTERED INDEX IX_MyIndex 
ON Employees(FirstName,LastName)
INCLUDE (FirstName);

Output:

Msg 1909, Level 16, State 2, Line 1
Cannot use duplicate column names in index. Column name 'FirstName' listed more than once.

Seeing as the column is already a key column, there’s no need to add it as a non-key column as well.

Solution

The solution is pretty easy; remove duplicate column references from the index definition.

For example, for the first one we can do this:

CREATE NONCLUSTERED INDEX IX_MyIndex 
ON Employees(FirstName,LastName);

Output:

Commands completed successfully.

All we did was remove the second LastName column from the definition.

And for the second one we could do this:

CREATE NONCLUSTERED INDEX IX_MyIndex 
ON Employees(LastName)
INCLUDE (FirstName);

Output:

Commands completed successfully.

That assumes we want LastName to be the key column and FirstName to be a non-key column.