Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?

user_1616023 0
Viewed 22042

Which one is the best choice for primary key in SQL Server?
There are some example code:

Uniqueidentifiers

e.g.

CREATE TABLE new_employees
   (employeeId   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   fname      VARCHAR(20) )
GO
INSERT INTO new_employees(fname) VALUES ('Karin')
GO

Identity columns

e.g.

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );

 INSERT new_employees
    (fname)
 VALUES
    ('Karin');

[Material Code](or Business Code,which identity of a material. e.g. customer identifier)

e.g.

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL      
 )

 INSERT new_employees
    (ClientID, fname)
 VALUES
    ('C0101000001',--customer identifier，e.g.'C0101000001' a user-defined code.
     'Karin');

Please give me some advices for choosing the primary key from the three type identity columns,or other choices.

Thanks!

sql-server-2012 database-design sql-server database
4 Answers

IDENTITY

PROS

  1. small storage footprint;
  2. optimal join / index performance (e.g. for time range queries, most rows recently inserted will be on a limited number of pages);
  3. highly useful for data warehousing;
  4. native data type of the OS, and easy to work with in all languages;
  5. easy to debug;
  6. generated automatically (retrieved through SCOPE_IDENTITY() rather than assigned);
  7. not updateable (though some consider this a disadvantage, strangely enough).

CONS

  1. cannot be reliably "predicted" by applications — can only be retrieved after the INSERT;
  2. need a complex scheme in multi-server environments, since IDENTITY is not allowed in some forms of replication;
  3. can be duplicated, if not explicitly set to PRIMARY KEY.
  4. if part of the clustered index on the table, this can create an insert hot-spot;
  5. proprietary and not directly portable;
  6. only unique within a single table;
  7. gaps can occur (e.g. with a rolled back transaction), and this can cause chicken little-style alarms.

GUID

PROS

  1. since they are {more or less} guaranteed to be unique, multiple tables/databases/instances/servers/networks/data centers can generate them independently, then merged without clashes;

  2. required for some forms of replication;

  3. can be generated outside the database (e.g. by an application);
  4. distributed values prevent hot-spot (as long as you don't cluster this column, which can lead to abnormally high fragmentation).

CONS

  1. the wider datatype leads to a drop in index performance (if clustered, each insert almost guaranteed to 'dirty' a different page), and an increase in storage requirements;
  2. cumbersome to debug (where userid = {BAE7DF4-DDF-3RG-5TY3E3RF456AS10});
  3. updateable (need to propogate changes, or prevent the activity altogether);
  4. sensitive to time rollbacks in certain environments (e.g. daylight savings time rollbacks);
  5. GROUP BY and other set operations often require CAST/CONVERT;
  6. not all languages and environments directly support GUIDs;
  7. there is no statement like SCOPE_GUID() to determine the value that was generated, e.g. by NEWID();
user_37924010
Related