Column in table is of a type that is invalid for use as a key column in an index

Question

I have a table where I am storing employee details. I want to alter the table and set one of the column emp_code as primary key. Its datatype is nvarchar(max), but I am not able to set it as primary key.

I run the following query :

ALTER TABLE user_master
ADD PRIMARY KEY (emp_code)

but it gives me an error :

Msg 1919, Level 16, State 1, Line 1
Column emp_code in table user_master is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

How can I overcome this?

Answer 1

An index's key cannot exceed a total size of 900 bytes. Change the data type to NVARCHAR(450). If that is not suitable, use a surrogate key (typically an IDENTITY column).