Many people find the GUID to be quite intimidating when they attempt to use the GUID-Key. GUIDs might be big, but they aren't so bad. A few tips can help.
Copying/Pasting GUIDs
When you are debugging, you can select code that contains a GUID and IntelliSense will show the GUID (Figure 5-4). You can select the GUID value and copy it to the clipboard. Paste this value into a query window, replacing the curly braces with single quotation marks, as shown in the following SQL statement.
Figure 5-4: Using IntelliSense to copy a GUID for pasting into query tools
SQL Query Using a GUID
SELECT Id, SSN, LastName, FirstName FROM TblAuthor WHERE (Id = 'cbc8c64c-6ba6-4bec-baef-4c0e50e8b251')
Using the Same Name for the Primary Key Column on Non-Join Tables
I strongly recommend that you use the same name (such as Id) for the primary key column of all of your non-join tables. This makes it easier to code stored procedures that work with GUIDs. Also, make it the first column in every table to help users understand the purpose of this field.
Finding a GUID in the Database
Depending on your database design, you might face situations where you are looking for a GUID in a foreign key column but have no idea where the data is for that GUID. An example of this is when you have an Exclusive-OR relationship, as shown in Figure 5-5. This type of relationship is typical in an object-oriented environment you might have a book class with various child classes such as EBook, PaperBack, and HardCover. These classes will have fields that are not common to each other, so you must choose to either create a single table with lots of null column values, or create a separate table for each child class as shown in Figure 5-5.
Figure 5-5: An example of an Exclusive-OR relationship
If you are trying to find out which table contains a specific GUID, you can use the following stored procedure to locate the table that has this GUID as a primary key value.
SQL uspGetDataForId
CREATE PROCEDURE dbo.uspGetDataForId ( @id uniqueidentifier ) AS SET NOCOUNT ON --NOTE: This proc assumes that all user tables have 'Tbl' prefix --Usage: in Query Analyser, type the following without the '--' --exec uspGetDataForId '78257ec8-c8f9-4d35-a636-d58d8a67c3d4' DECLARE @tbl varchar(2000) DECLARE @sql varchar(2000) IF OBJECT_ID('tempdb..#idTable') IS NOT NULL DROP TABLE #idTable CREATE TABLE #idTable ( Id uniqueidentifier, Count INT, TableName varchar(2000) ) DECLARE tables_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.Tables WHERE substring (TABLE_NAME,1,3)='Tbl' OPEN tables_cursor FETCH NEXT FROM tables_cursor INTO @tbl WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS (SELECT * FROM information_schema.columns WHERE table_name=@tbl AND Column_Name='Id') BEGIN SET @sql = 'INSERT INTO #idTable SELECT id as ''Id'', ' + 'count(*) as ''Count'',''' + @tbl +''' as ''TableName'' FROM ' + @tbl + ' WHERE ID=''' + CONVERT(varchar(2000),@id) + ''' group by Id' EXEC(@sql) END FETCH NEXT FROM tables_cursor INTO @tbl END CLOSE tables_cursor DEALLOCATE tables_cursor SELECT Id, TableName FROM #idTable WHERE Count > 0
Note that this stored procedure relies on all primary key columns being named Id and the user tables having a Tbl prefix. This stored procedure does not attempt to find a GUID in any other column, but if you need to find all usages for a GUID, read on.
Finding All Usages of a GUID in the Database
You often want to find all usages of a GUID. The following SQL script enumerates all of the user tables with a Tbl prefix and queries all columns with a uniqueidentifier data type.
SQL uspGetUsagesForId
CREATE PROCEDURE dbo.uspGetUsagesForId ( @id uniqueidentifier ) AS --NOTE: This proc assumes that all user tables have 'Tbl' prefix --Usage: in Query Analyser, type the following without the '--' --exec uspGetUsagesForId '78257ec8-c8f9-4d35-a636-d58d8a67c3d4' SET NOCOUNT ON DECLARE @tbl varchar(2000) DECLARE @sql varchar(2000) DECLARE @counter integer IF OBJECT_ID('tempdb..#guidTable') IS NOT NULL DROP TABLE #guidTable CREATE TABLE #guidTable ( Id uniqueidentifier, Count INT, TableName varchar(2000) ) DECLARE tables_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.Tables WHERE SUBSTRING(TABLE_NAME,1,3)='Tbl' OPEN tables_cursor FETCH NEXT FROM tables_cursor INTO @tbl WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT INTO #guidTable SELECT id as ''Id'', ' + 'count(*) as ''Count'',''' + @tbl +''' as ''TableName'' FROM ' + @tbl + ' WHERE ' + dbo.fnGetGuidWhereClause(@tbl, @id) + ' GROUP BY ID' EXEC(@sql) SELECT @counter = COUNT(*) FROM #guidTable IF @counter > 0 BEGIN SET @sql = 'SELECT ''' + @tbl + ''' as TABLE_NAME, * FROM ' + @tbl + ' WHERE ' + dbo.fnGetGuidWhereClause(@tbl, @id) EXEC(@sql) END DELETE FROM #guidTable FETCH NEXT FROM tables_cursor INTO @tbl END CLOSE tables_cursor DEALLOCATE tables_cursor CREATE function dbo.fnGetGuidWhereClause ( @TableName varchar(500), @Id uniqueidentifier ) returns varchar(2000) as begin DECLARE @currentColumn varchar(2000) DECLARE @whereClause varchar(2000) SET @whereClause = ' ' DECLARE columns_cursor CURSOR FOR SELECT COLUMN_NAME FROM information_schema.Columns WHERE TABLE_NAME = @TableName AND DATA_TYPE='uniqueidentifier' OPEN columns_cursor FETCH NEXT FROM columns_cursor INTO @currentColumn WHILE @@FETCH_STATUS = 0 BEGIN if(@whereClause) <> ' ' BEGIN SET @whereClause = @whereClause + ' OR ' END set @whereClause = @whereClause + @currentColumn + '=''' + convert(varchar(2000),@id) + '''' FETCH NEXT FROM columns_cursor INTO @currentColumn END CLOSE columns_cursor DEALLOCATE columns_cursor return @whereClause end
If you think about the benefits of these stored procedure tricks, you will realize that you can't accomplish these tricks and get the same results with the other primary key implementations.
