# Tuesday, 07 July 2015

Every now and then an error with only a GUID as part of the information comes up on a screen... The question is, does it live in my database and if so, where (and how) to find it? Today was one of those days and I quickly wrote a query to find out where the particular uniqueidentifier sits in de SQL Server database.

A quickly written query did the job, but I had this feeling it was suboptimal. The way I wrote it, required three scans or seeks on the source table. You’ll hardly notice the difference in AdventureWorks, but the faster (and resource-friendlier) the result is available, the better.

Typical query result for GUID in SQL database

DECLARE @guid uniqueidentifier
SELECT
@guid = 'E552F657-A9AF-4A7D-A645-C429D6E02491'
SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name
   , OBJECT_NAME(c.object_id) AS table_name
   , c.name AS column_name
   , @guid AS value
   , CAST(NULL AS bigint) AS value_count
   , CAST(NULL AS bigint) AS notnull_count
   , CAST(NULL AS bigint) AS row_count
   , CAST(NULL AS nvarchar(max)) AS preview_query
INTO ##hunt_for_guid
FROM sys.columns c
    INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
    INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE t.name = 'uniqueidentifier' AND o.type = 'U'
WHILE (SELECT COUNT(*) FROM ##hunt_for_guid WHERE row_count IS NULL) > 0
BEGIN
DECLARE
@sql nvarchar(max)
   /*
-- Not optimal, requires reading the source table/index 3 times
SELECT @sql = 'UPDATE ##hunt_for_guid SET value_count = (SELECT COUNT(' + QUOTENAME(column_name) + ') FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' = ''' + CAST(@guid AS nvarchar(36)) + ''')
       , notnull_count = (SELECT COUNT(' + QUOTENAME(column_name) + ') FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ')
       , row_count = (SELECT COUNT(*) FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ')
       , preview_query = ''SELECT * FROM ' + QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' = ''''' + + CAST(@guid AS nvarchar(36)) + '''''''
       WHERE [schema_name] = ''' + schema_name + ''' AND [table_name] = ''' + table_name + ''' AND [column_name] = ''' + column_name + ''''
       FROM ##hunt_for_guid WHERE row_count IS NULL
*/
-- Optimized by scanning the source table/index only once.
SELECT @sql = 'UPDATE H
SET H.value_count = S.value_count, H.notnull_count = S.notnull_count, H.row_count = S.row_count
, H.preview_query = ''SELECT * FROM '
+ QUOTENAME(schema_name) + '.' + QUOTENAME(table_name) + ' WHERE ' + QUOTENAME(column_name) + ' = ''''' + CAST(@guid AS nvarchar(36)) + '''''''
FROM ##hunt_for_guid AS H INNER JOIN (SELECT
SUM(CASE WHEN '
+ column_name + ' = ''' + CAST(@guid AS nvarchar(36)) + ''' THEN 1 ELSE 0 END) AS value_count,
COUNT('
+ column_name + ') AS notnull_count, COUNT(*) AS row_count FROM ' + schema_name + '.' + table_name + ') AS S ON
H.schema_name = '''
+ schema_name + ''' AND H.table_name = ''' + table_name + ''' AND H.column_name = ''' + column_name + ''''
FROM ##hunt_for_guid WHERE row_count IS NULL
EXEC (@sql)
   END
SELECT
* FROM ##hunt_for_guid ORDER BY value_count DESC, schema_name, table_name, column_name
DROP TABLE ##hunt_for_guid