GUIDs

Distributed environments have different requirements for the generation of unique keys. A typical example is a database of sales representatives who are carrying notebook computers with local databases installed on them. These users do not have to be connected to a central database. They do the majority of their work locally and then replicate the information in their local database to the central database once in a while. The use of identity fields as a unique key will lead to unique key violations, unless the key is composite and consists of an identity field and another field that is unique to the user. Another solution could be to divide key ranges between users (for example, by setting an identity seed differently in each database). Each of these solutions has different limitations.

One way to generate unique keys is to use GUIDs (globally unique identifiers). You can find more details about the uniqueidentif ier data type in Appendix A. When a column in a table is assigned this data type, it does not mean that its (unique) value will be generated automatically. The unique value must be generated using the NewID() function.

Typically, a GUID value is generated as a default value of a table, as shown in the following code:

      Create Table Location!       LocationId uniqueidentifier NOT NULL DEFAULT newid(),       Location varchar(50) not null,       CompanyId int NOT NULL,       PrimaryContactName varchar(60) NOT NULL,       Address varchar(BO) NOT NULL,       City varchar(BO) NOT NULL,       ProvinceId varchar(B) NULL,       PostalCode varchar(10) NOT NULL,       Country varchar(20) NOT NULL,       Phone varchar(15) NOT NULL,       Fax varchar(15) NULL      )      Go 

You can also generate a GUID in a stored procedure:

      Create Procedure dbo.ap_Location_Insert       @Location varchar(50),       @CompanyId int,       @PrimaryContactName varchar(60),       @Address varchar(BO) ,       @City varchar(BO) ,       @ProvinceId varchar(B) ,       @PostalCode varchar(10),       @Country varchar(20) ,       @Phone varchar(15),       @Fax varchar(15),       @LocationGUID uniqueidentifier OUTPUT      AS      Set @LocationGUID = NewId()      Insert Into dbo.Location (Location_id, Location, CompanyId,                               PrimaryContactName, Address, City,                               ProvinceId, PostalCode, Country,                               Phone, Fax)      values (@LocationGUID, @Location, @CompanyId,              @PrimaryContactName, @Address, @City,              @ProvinceId, @PostalCode, @Country,              @Phone, @Fax)      Return @@error 

The stored procedure will return the GUID value to the caller.