Surrogate keys - system generated primary keys

This post should be a quick reference to how to generate surrogate keys in SQL Server.


(1)   IDENTITY (column property) 
 :  allows the database to generate keys for the column of a numeric types such as INT, BIGINT, SMALLINT, TINYINT.

   CREATE TABLE   guests
       (  GuestId      int    IDENTITY (100, 1)           -- starting from 100 and increment by 1
        , Firstname    nvarchar (200)
         ,Lastname    nvarchar (200)
         ,Email          nvarchar (100) )

   INSERT INTO guests(Firstname, Lastname, Email)
        VALUES('Hoon', 'Lee', 'hoonerLee@hoon.com');

   SELECT * FROM guests;

   Studentid    Firstname    Lastname    Email
   100             Hoon          Lee             hoonerLee@hoon.com


(2) Sequence (database object)
  : an independent object that can be shared by all the tables in the entire database. Numeric types.


   Create SEQUENCE dbo.GuestID
       AS BIGINT
       START WITH    1000
       INCREMENT BY   1 ;
   GO

   DECLARE @guestID as INT;
   SELECT @guestID = NEXT VALUE FOR dbo.GuestID;

   SELECT @guestID as GuestID;     ->   10001
   SELECT @guestID as GuestID;     ->   10002
   ...
   SELECT @guestID as GuestID;     ->   10011


(3) Non-sequential GUIDs (Global Unique Identifiers)  
  :   For UNIQUEIDENTIFIER type of column. Use NEWID() function to get a new GUID  in INSERT statement or create a  UNIQUEIDENTIFIER column with NEWID() as DEFAULT constraint.


CREATE TABLE students
( studentID    UNIQUEIDENTIFIER  NOT NULL
 , Name         VARCHAR(30)   );
 
INSERT students (studentid, name) VALUES ( NEWID() ,  'Hooner');

OR

CREATE TABLE students
( studentID    UNIQUEIDENTIFIER  DEFAULT   NEWID()
 , Name         VARCHAR(30)   );
 
INSERT students ( name) VALUES (  'Hooner');

SELECT * FROM students;
studentID                                                                          Name
A5971324-30BF-4781-B2B2-DA8C7B36C18F             Hooner
4F79B592-3CCF-4C11-B85F-DCFFB2BD84D7           Hooner
F4D94A6B-9D5A-4D3C-A590-B91F3D462E2F           Hooner



(4) Sequential GUIDs
  :   For UNIQUEIDENTIFIER type of column. Use NEWSEQUENTIALID() function with DEFAULT constraints of table columns to get a new GUID.

** The database generates a GUID that is greater than any GUID previously generated by the function since Windows was started. Whenever Windows restarts, the GUID starts again from a lower range, but it is still globally unique. Becuase GUIDs are generated sequentially, one can easily guess the next new GUID. This can become a privacy/security concern.  NEWSEQUENTIALID is a wrapper over the Windows UuidCreateSequential function.

CREATE TABLE students
( studentID        UNIQUEIDENTIFIER     DEFAULT     NEWSEQUENTIALID()
 , Name             VARCHAR(30)   )

INSERT students ( name) VALUES (  'Hooner')

select * from students

studentID                                                                    Name
AD27E74A-1862-E311-9B46-0023AE0AD326       Hooner
AE27E74A-1862-E311-9B46-0023AE0AD326       Hooner
AF27E74A-1862-E311-9B46-0023AE0AD326       Hooner
B027E74A-1862-E311-9B46-0023AE0AD326        Hooner
B127E74A-1862-E311-9B46-0023AE0AD326        Hooner
8B48F850-1862-E311-9B46-0023AE0AD326         Hooner

------------------
Reference: Querying Microsoft SQL Server 2012 Training Kit by Itzik Ben-Gan, Dejan Sarka, Ron Talmage
http://technet.microsoft.com