Who s Afraid of the Big, Bad GUID

 

Who's Afraid of the Big, Bad GUID?

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.

image from book
Figure 5-4: Using IntelliSense to copy a GUID for pasting into query tools

image from book

SQL Query Using a GUID

SELECT Id, SSN, LastName, FirstName FROM   TblAuthor WHERE  (Id = 'cbc8c64c-6ba6-4bec-baef-4c0e50e8b251') 
image from book

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.

image from book
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.

image from book

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 
image from book

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.

image from book

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 
image from book

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.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net