Define Identity and uniqueidentifier property of Column.Identity Column
- Column with identity property contains unique system generated value in the table.
- Column with identity property is similar to AutoNumber field in MS Access.
- You can set start and increment value while setting column property to identity.
- Column with identity property contains unique values within the table.
- The column with uniqueidentifier property contains globally unique data.
- SQL server uses uniqueidentifier property for merge replication.
- Same like Identity Column, SQL server supports uniqueidentifier column that also contains system generated value.
- A column or local variable of uniqueidentifier data type can be initialized using NEWID function.
Define Identity and uniqueidentifier property of Column.Uniqueidentifier: They are also known as GUIDs and are guaranteed to return unique values across space and time. It is natively stored as a 16 byte binary value. On performing an insert operation, Uniqueidentifier does not get automatically generated for the new row, instead the user has to mention a statement like SET @Guid=NEWID() to get the new value. Indexes built on them are bigger and slower since they are a big datatype.
Identity: Identity property allows automatic generation of a unique number. The user does not have to code a statement for a newly inserted row to get its identity value. It’s natively a 4 byte integer value and hence enables indexes built on them to be smaller and much faster than the UniqueIdentifiers.