IDENTITY vs UNIQUEIDENTIFIER COLUMN
IDENTITY
Sl
No
|
Advantage
|
Disadvantage
|
1
|
Identity
column stores numeric values. Numeric value column have
better performance in joins, indexes and conditions.
|
Range
of values stored in identity column based on data type :
bigint
2^63
(-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
int
-2^31
(-2,147,483,648) to 2^31-1 (2,147,483,647)
smallint
-2^15
(-32,768) to 2^15-1 (32,767)
tinyint
0
to 255
If table size is large, it is quite possible it will run out
of it and after some numeric value there will be no additional identity to
use.
|
2
|
Numeric values are easier to understand for application users
if they are displayed.
|
UNIQUEIDENTIFIER
Sl No
|
Advantage
|
Disadvantage
|
1
|
·
Unique across server
|
Uniqueidentifier
column has string value i.e. {BAE7DF4-DDF-3RG-5TY3E3RF456AS10}. String
values are not as optimal as integer values for performance when used in
joins, indexes and conditions.
|
2
|
·
Since it is unique across server , allows easy
merging of records from different databases
|
More
storage space is required than INT
|
3
|
·
Allows easy distribution of databases across
multiple servers
|
|
4
|
·
You can generate IDs anywhere, instead of having
to roundtrip to the database
|
|
5
|
Most
replication scenarios require GUID columns anyway
|
No comments:
Post a Comment