Difference between identity and Guid in sql server

Identity:  When we make any column property as  identity, sql server will automatically assign sequence number to that column for every row you will be inserted.

  • An identity column must be numeric.
  • Mostly use primary key with identity but it’s not necessary that an identity column have primary key.

To know more about identity : Sql Server Identity

As we know identity does not mean the unique value. you can always reset seed value and count backup to old value as before. 

ROWGUIDCOL: this is also used to uniquely identify each row in a table similar to identity. the difference is what length the system goes to make the value truly unique.Instead of numeric counter SQL Server use unique identifier (GUID).

While and identity usually unique across the time like the two table of a table may have running the identical identity values. It cause a big problem when you try to bring the rows of both table together as one replicated table. To get rid of this we have to use GUID.

GUID: it’s a 36  charetect unique number are generated using the combination of information and its unique across the time & space. its “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx” in which each x is a hexadecimal digit in the range 0-9 or a-f.

  • No other arithmetic operators can be used other than comparison (=, <>, <, >, <=, >=) and checking null or not null.
  • All column constraints and properties, except IDENTITY, can be used on the uniqueidentifier data type.
  • uniqueidentifier type is considered a character type for the purposes of conversion.