11

Varchar(max) column not allowed to be a Primary Key in SQL Server.

What is the max length of a varchar type that can be made a primary key.

This might be a bad practice, but came across the situation.

blogbydev
  • 1,445
  • 2
  • 17
  • 29
  • 3
    Why do you not have an ID column? – Kirk Woll May 11 '12 at 17:02
  • You aren't using varchar(max) for anything except fields that you expect to be over 8000 characters are you? You will have performance issues if so due to the indexing issues. – HLGEM May 11 '12 at 20:14

3 Answers3

22

900 bytes is the max length for a primary key, this is a bad idea in my opinion, why don't you use a surrogate key instead?

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
6

This question has already been answered in this thread; the short answer is that an index field cannot exceed 900 bytes. That said, it's generally a bad idea to use a long VARCHAR field as an index for myriad reasons such as efficiency and maintainability.

Community
  • 1
  • 1
Jgreenst
  • 96
  • 1
  • 4
3

The primary key is used in all other indexes to identify rows. Having a potentially multi-megabyte value as a key makes very little sense, hence the limit in general on total bytes in any key.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265