GUID is a way to ensure uniqueness of the values. It finds use in various scenario’s. GUID is produced by using a combination of Time and Network Address so there is no chance of getting a duplicate GUID. Microsoft guarantees GUID’s to be unique for the next century.

Some of the common scenario’s are:

• Data is collected on many servers and merged in one single source.
• PDA’s being used by Sales Representatives to store data while on move. They push the data back to CRM system when they have connectivity. SQL Server CE supports GUID’s and this makes it possible for GUID’s to be used extensively in applications made for PDA’s.
• Editable Data is spread across many database Servers, a periodic replication is scheduled for same to update data on each of the servers.
• Companies trying to consolidate data on one single server after holding data on several servers.

Drawbacks of GUID

• GUID requires 16 bytes to be stored which means four times the space required for an an auto increment integer which is an alternate way of maintaining the uniqueness if the scope is limited to one server.
• Indexes, Primary Keys built on GUID degrade the performance of system due to more space requirements and hence more time required to update Indexes.
• GUID values are difficult to interpret so it becomes difficult to work with them directly.
• GUID does not follow a definite pattern so it cannot be used in any sort sequence.
• GUID cannot participate in queries involving Aggregate Function. GUID needs to be converted to Character to be used in aggregate function.
• It is not possible to convert the structure of the Table with Integer data type as Primary Key to GUID directly as the two data types are not compatible. In such a scenario, It is required to change the structure of the table to Character(32). Populate the table with GUID values using NewId() function and then convert the structure to GUID.

Scripts to demonstrate the same:

if exists (select * from dbo.sysobjects 
             where id = object_id(N'[dbo].[tblEntries]') 
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[tblEntries]
GO
CREATE TABLE [dbo].[tblEntries] (
    [IValue] [int] NOT NULL
    [userValue] bit not null default 0
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEntries] ADD 
      CONSTRAINT [PK_tblEntries] PRIMARY KEY CLUSTERED
      ( [IValue]
      ) ON [PRIMARY] 
GO
--Make Entries in to Table
Insert into tblEntries values (1,0)
Insert into tblEntries values (2,0)
Insert into tblEntries values (3,0)
Insert into tblEntries values (4,0)
Insert into tblEntries values (5,1)
GO
if exists (select * from dbo.sysobjects 
            where id = object_id(N'[PK_tblEntries]') 
and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
   ALTER TABLE [tblEntries] DROP CONSTRAINT [PK_tblEntries] 
Alter table tblentries alter column IValue Char(36)
--This field can be used later to draw correlation between GUID’s generated 
-- and the original Ivalue.
Alter table tblentries add column IvalueOrig Integer
Update tbleEntries Set IvalueOrig = Ivalue
Update tblEntries set IValue = NewId()
GO
if exists (select * from dbo.sysobjects 
           where id = object_id(N'[PK_tblEntries]') 
  and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
   ALTER TABLE [tblEntries] DROP CONSTRAINT [PK_tblEntries]
Alter table tblentries alter column IValue Char(36)
Update tblEntries set IValue = NewId()
Alter table tblEntries Alter column [IValue] [UNIQUEIDENTIFIER] not null
GO
ALTER TABLE [dbo].[tblEntries] 
    ADD CONSTRAINT [DF_tblEntries_IValue]
           DEFAULT (newid()) FOR [IValue],
   CONSTRAINT [PK_tblEntries] PRIMARY KEY CLUSTERED
        ( [IValue]
        ) ON [PRIMARY] 
GO

There are number of Database Applications in which the user has the right to add new rows but there are some rows which are already existing and they need to have the same id across different installations. In such a scenario one of the servers becomes the master and the same Id needs to be applied to number of servers using scripts. In the table created above, we are assuming that all the rows with uservalue = 0 are the ones which should have the same id and the ones with uservalue = 1 can be added by the user so they don’t need to have the same id. In such a scenario a single script needs to be prepared to be applied across database’s. This can be achieved by applying NewID() function on one of the master Database’s and then the values generated on this Database’s corresponding to original Id be applied across on other Databases.

Demonstrating the same with scripts

Run the following Script to get a set of Update Statements to be applied on other database’s:

Select 'Update tblEntries Set Ivalue='''+convert(varchar(36),Ivalue)
    + ''' where Ivalue = '+convert(varchar(4),ivalueorig) 
 +' and UserValue = 0' from tblEntries

A set of Update statements will be generated which can be used in Block A to apply to all other Database to have the same ID’s replicated across different installations. Hence the script for the slave database’s shall be as follows:

if exists (select * from dbo.sysobjects 
           where id = object_id(N'[dbo].[tblEntries]') 
  and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   drop table [dbo].[tblEntries]
GO
CREATE TABLE [dbo].[tblEntries] 
(  [IValue] [int] NOT NULL
   [userValue] bit not null default 0
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblEntries] ADD 
   CONSTRAINT [PK_tblEntries]
     PRIMARY KEY CLUSTERED
     (  [IValue]
     )
ON [PRIMARY] 
GO
--Make Entries in to Table
Insert into tblEntries values (1,0)
Insert into tblEntries values (2,0)
Insert into tblEntries values (3,0)
Insert into tblEntries values (4,0)
Insert into tblEntries values (5,1)
if exists (select * from dbo.sysobjects 
           where id = object_id(N'[PK_tblEntries]') 
  and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
  ALTER TABLE [tblEntries] DROP CONSTRAINT [PK_tblEntries] 
Alter table tblentries alter column IValue Char(36)
--This field can be used later to draw correlation between GUID’s generated and the 
--original Ivalue.
Alter table tblentries add column IvalueOrig Integer
Update tblEntries 
  Set Ivalue='870563A9-83C4-4193-A4B9-1E8683887E46'
where Ivalue = 3
and UserValue = 0
Update tblEntries 
  Set Ivalue='BF05A46C-70FE-4ADC-83F2-95C3761CFB7A'
  where Ivalue = 4
  and UserValue = 0
Update tblEntries 
  Set Ivalue='EB3EFF74-110A-48C3-ACF1-E5ECE82E8AAB'
  where Ivalue = 1
  and UserValue = 0
Update tblEntries 
  Set Ivalue='F8308370-D493-40E3-9048-E79DC6C614D7'
  where Ivalue = 2
  and UserValue = 0
Update tblEntries 
  set IValue = NewId()
 where uservalue =1
GO
if exists (select * from dbo.sysobjects 
           where id = object_id(N'[PK_tblEntries]') 
  and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [tblEntries] DROP CONSTRAINT [PK_tblEntries] 
Alter table tblentries alter column IValue Char(36)
Update tblEntries set IValue = NewId()
Alter table tblEntries Alter column [IValue] [UNIQUEIDENTIFIER] not null
GO
ALTER TABLE [dbo].[tblEntries] ADD 
   CONSTRAINT [DF_tblEntries_IValue]
       DEFAULT (newid()) FOR [IValue],
   CONSTRAINT [PK_tblEntries]
       PRIMARY KEY CLUSTERED
       ( [IValue]
       ) ON [PRIMARY] 
GO

Unique Identifiers should be used with caution as they impact performance of the Database. However with WI-FI technology becoming more popular and usage of hand held devices increasing, GUID shall be used more extensively in many applications.