3

I know it's not a good idea, but I would like to double check that this won't do something crazy like crash the server. 

CREATE TABLE [dbo].[Items](
    [Id] [nvarchar](255) NOT NULL PRIMARY KEY,
    [Value] [nvarchar](max) NOT NULL,
)

It would be interesting to know details about how a key like this compares to an [int] key, but just confirmation that this will not hurt anything is sufficient.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Aaron Anodide
  • 16,906
  • 15
  • 62
  • 121
  • Since your primary key is also your **clustering key** on that table, having a `VARCHAR(255)` PK is horribly bad for two reasons: (1) it's much too big (up to 255 bytes plus overhead) vs. 4 bytes for an `INT`, and secondly, since it's a variable length column, that again adds more overhead to the index navigation structures. If this is a table with several nonclustered indices, I would **strongly urge** to rethink your primary key .... – marc_s Mar 20 '12 at 06:06
  • 3
    Marc has mentioned a reason why not to use Id for a *clustered index*. Despite what he says, that's no reason for it not to be a primary key. A primary key doesn't have to be clustered. – nvogel Mar 20 '12 at 06:51

3 Answers3

6

Can you use VARCHAR(#) as a primary key?

Yes.
From the documentation, a VARCHAR(255) will take upwards of 257 (255+2) bytes (depends on the length of the actual data), for that column -- per row.

It would be interesting to know details about how a key like this compares to an INT key

INT takes 4 bytes, according to documentation. Depending on your data, there are numeric data types that take less space:

  • SMALLINT: 2 bytes
  • TINYINT: 1 byte

The lower the number of bytes, the faster accessing data in the column will be. That includes JOINs. Additionally, the database (and backups) will be smaller.

I would greatly question the need for such a large VARCHAR as the primary key -- GUIDs aren't even that long.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • i'm using it as a convenience to get something done fast - I know it's a poor design choice in the long run - thanks for the advice... – Aaron Anodide Mar 20 '12 at 02:27
1

No, you can create a primary key on almost any SQL type. SQL will not allow it if it would crash the server. As you already stated, this will not be great for the server memory or performance, though.

In fact, here is the SO answer to the performance part of this question.

Community
  • 1
  • 1
Justin Pihony
  • 66,056
  • 18
  • 147
  • 180
0

There is a performance overhead with this, but it won't 'crash the server'.

hkf
  • 4,440
  • 1
  • 30
  • 44