Geeks With Blogs
Stephen Myers Good Times with .Net

When using uniqueidentifiers as Primary keys, you get the bonus of being able to write a mutli-function Read stored procedure.  Lets take a look at a table used for storing client Addresses: 

-- ================================================
CREATE TABLE
[Address](
[AddressId] [uniqueidentifier]
NOT NULL DEFAULT NEWID(),
[ClientId] [uniqueidentifier]
NOT NULL,
[AddressTypeId] [uniqueidentifier]
NOT NULL,
[StreetLine1] [varchar](50),
[StreetLine2] [varchar](50),
[ZipCode] [int],
[LastRevisedDate] [datetime]
NOT NULL DEFAULT GETDATE(),
[LastRevisedUser] [uniqueidentifier]
NOT NULL
CONSTRAINT
[PK_Address] PRIMARY KEY NONCLUSTERED ([AddressId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GO 
 -- ================================================

Assume that the ClientId is a link to the client table, the adressTypeID is a link to a type table (think Home, Business, etc) and the LastRevisedUser is a link to a users table to record who last updated this record.  Instead of writting four seperate procedures to get Addresses by each of these ID's, since you are using uniqueidentifiers, you can write a single stored procedure that would retrieve addresses by any of these criteria.

-- ================================================
IF OBJECT_ID ( 'Address_GetById', 'P' ) IS NOT NULL
DROP PROCEDURE Address_GetById;
GO
CREATE PROCEDURE
Address_GetById
@Id uniqueidentifier
AS
BEGIN
-- =======================================================================
-- CRUD Code Stored Procedure - [Address_GetById]
-- =======================================================================
SELECT A.[AddressId]
,A.[ClientId]
,A.[AddressTypeId]
,C.[Description]
AS AddressDescription
,A.[StreetLine1]
,A.[StreetLine2]
,A.[ZipCode]
,A.[LastRevisedDate]
,A.[LastRevisedUser]
FROM [Address] A
LEFT OUTER JOIN Code C ON A.AddressTypeId = C.CodeId
WHERE
AddressId = @Id OR ClientId = @Id OR A.[AddressTypeId] = @Id OR A.[LastRevisedUser] = @Id
END-- ================================================

 This will save you not only on the stored procedure side of things, but also cut out boilerplate code when you are writing the data layer methods to call stored procedures to get the address or addresses by these different criteria.  In this case you turn 4 CRUD Read stored procedures into 1, and cut out the all of the associated cut & paste in the data layer.  You could also let a NULL parameter mean "Give me all the Addresses" if you add another WHERE clause "OR @Id IS NULL"

  

Posted on Monday, February 23, 2009 9:09 AM | Back to top


Comments on this post: One joy of uniqueidentifier PK's

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © smyers | Powered by: GeeksWithBlogs.net