Sql-server – Are GUIDs damaged when inserted into Varchar fields

sql servert-sql

In my staging database, I did the "smart thing", and used NewID() as a default value for Document_ID. Historically, that GUID has been generated by a fragile .NET script, so I am redesigning the process using database operations. It worked well, up to the end.

The data from staging are pasted to production after testing, through SSMS (We have a lot of NOT NULLS in the schema, and it's an easy way to get around them).

On the production system, the Document_ID is a Varchar(100). After the paste, the GUID no longer works in queries, but only as a string:

 -- this format works
select * from Doc_Main where Document_ID like '%B20DC300-DC3C-40D9-AC83-4756C96F83DC%'
 -- Same format as below, doesn't work
select * from Doc_Main where Document_Id ='{B20DC300-DC3C-40D9-AC83-4756C96F83DC}'

Operationally, this is breaking the relationship between Doc_Main and the detail tables.

I am thinking that I will try to generate proper Insert statements based on the Staging data for Doc_Main, and replace the pasted records before doing anything more serious.

Ideas?

Best Answer

Under normal circumstances, a VARCHAR and a UNIQUEIDENTIFIER should implicitly convert in both directions.

However, in your production system, you're comparing two character strings. That's problem one

Problem two is that the formatting you're using (the braces) doesn't make the string into a GUID. If you're comparing a string formatted that way, it will match a GUID with that value (without the braces), but when you do a text comparison, 'X' <> '{X}'.

See the examples below to confirm:

DECLARE @my_guid uniqueidentifier = 'C75F516F-971A-4752-B25B-B9DB877FAE17';
DECLARE @my_varchar varchar(100) = @my_guid;

SELECT @my_guid as [GUID], @my_varchar as [Varchar];

SELECT CASE WHEN (@my_guid = 'C75F516F-971A-4752-B25B-B9DB877FAE17') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_Regular
      ,CASE WHEN (@my_guid = '{C75F516F-971A-4752-B25B-B9DB877FAE17}') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_Braces
      ,CASE WHEN (@my_guid = {guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'}) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_GUID_ODBC
      ,CASE WHEN (@my_varchar = 'C75F516F-971A-4752-B25B-B9DB877FAE17') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_Regular
      ,CASE WHEN (@my_varchar = '{C75F516F-971A-4752-B25B-B9DB877FAE17}') THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_Braces
      ,CASE WHEN (@my_varchar = {guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'}) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Char_ODBC
      ,CASE WHEN (@my_varchar = CAST('C75F516F-971A-4752-B25B-B9DB877FAE17' as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_Regular
      ,CASE WHEN (@my_varchar = CAST('{C75F516F-971A-4752-B25B-B9DB877FAE17}' as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_Braces
      ,CASE WHEN (@my_varchar = CAST({guid 'C75F516F-971A-4752-B25B-B9DB877FAE17'} as uniqueidentifier)) THEN 'Matches' ELSE 'Doesn''t Match' END as Test_Cast_ODBC
;

The results show that comparing the string in braces to an actual GUID, or converting it into a GUID before comparing, works when it's compared to a string; comparing it to a string directly fails.

GUID                                 Varchar
------------------------------------ -------------------------------------
C75F516F-971A-4752-B25B-B9DB877FAE17 C75F516F-971A-4752-B25B-B9DB877FAE17

Test_GUID_Regular Test_GUID_Braces Test_GUID_ODBC 
----------------- ---------------- -------------- 
Matches           Matches          Matches        

Test_Char_Regular Test_Char_Braces Test_Char_ODBC
----------------- ---------------- --------------
Matches           Doesn't Match    Matches       

Test_Cast_Regular Test_Cast_Braces Test_Cast_ODBC
----------------- ---------------- --------------
Matches           Matches          Matches
Related Question