10

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.

singsuyash
  • 1,387
  • 2
  • 14
  • 27
  • 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

21

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
  • 128,762
  • 24
  • 200
  • 224
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
  • 85,870
  • 40
  • 177
  • 264