17

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
hitesh.gawhade
  • 337
  • 3
  • 6
  • 21
  • 2
    Blob column can not be used for indexing, they need to be of the fixed size. – Farfarak Oct 30 '12 at 10:41
  • 2
    Does it need to be MAX? SQL Server is telling you that you can't have that as a key type. Why not just shorten it to something useful? – Preet Sangha Oct 30 '12 at 10:41
  • Your emp_code column is too wide: http://stackoverflow.com/questions/2863993/is-of-a-type-that-is-invalid-for-use-as-a-key-column-in-an-index – SchmitzIT Oct 30 '12 at 10:42

3 Answers3

31

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).

ta.speot.is
  • 26,914
  • 8
  • 68
  • 96
  • wild guess of the required size ;), what if requires all 2 GB of it. – Farfarak Oct 30 '12 at 10:42
  • 1
    @010001100110000101110010011010 It's the largest possible size for a primary key of `NVARCHAR(x)`. – ta.speot.is Oct 30 '12 at 10:42
  • Great !! size was the issue. I set it to 450, (which is max) and it worked. Thanks. – hitesh.gawhade Oct 30 '12 at 10:44
  • Very true size limitation is 900 bytes for the keys. – Farfarak Oct 30 '12 at 10:47
  • Also: even if you **technically** can make a `NVARCHAR(450)` column your primary key - if you happen to also make that your **clustering key** on that table - you'll have **horribly bad** performance all around. All your nonclustered indices will be huge and inefficient, and your queries will need to read a lot more pages than actually necessary. A **good clustering key** should be **NARROW** - heck, I'm even saying 16 byte for a GUID is too much! Don't use overly large and variable length clustering keys! – marc_s Oct 30 '12 at 12:36
6

You can't use varchar(max). It isn't designed to store primary keys.

Find out what the longest employee code, and create a sized nvarchar that covers your longest case.

e.g. longest employee code is 70 characters, try:-

nvarchar(100)

...just to cover yourself.

Paul Alan Taylor
  • 10,474
  • 1
  • 26
  • 42
1

Yes you are correct.Warning! The maximum key length is 900 bytes. The index PK__bookmark__2DFA2B0E164452B1 has maximum length of 2000 bytes. For some combination of large values, the insert/update operation will fail. not use of varchar(max)

Konrad Krakowiak
  • 12,285
  • 11
  • 58
  • 45
dheeraj
  • 11
  • 1