SQL Server Identity and uniqueidentifier

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.

Uniqueidentifier, GUID
- 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.
Stuff and Replace
Stuff Function - It replaces existing character. - STUFF(string_expression, start, length, replacement_characters).....
Execution Plan and SQL Profiler
Execution Plan and SQL Profiler - Describe in brief SQL Server monitoring ways. We have answered this question in this section
Transaction Isolation Levels
SQL Server Transaction Isolation Levels - Define transaction and transaction isolation levels.....
Post your comment