3

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?

1
  • 1
    Yes, either set up a linked server and do a proper INSERT INTO prodtable SELECT * FROM devserver.dbname.dbo.devtable or just use something like SSIS to move the records.
    – BradC
    Jun 9 '17 at 20:19
4

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
1
  • Very helpful. Thanks! Those types do seem to implicitly convert, and you ID'd my not GUID issue, too.
    – Ken Lyle
    Jun 12 '17 at 13:52
2

In my staging database, I did the "smart thing", and used NewID() as a default value for Document_ID.

Not to be critical, but: No, using GUIDs was not the "smart" thing to do. You should create both test data and lookup values in a controlled manner. In both cases an INT or even BIGINT is perfect. If it is lookup data (i.e. company supplied for the app) then do not use IDENTITY. Use rollout scripts that have pre-defined ID values and can be applied to all environments equally. If it is user-supplied data, then it will be an IDENTITY (in most cases, at least) in which case your rollout script would specify a negative value (assuming the IDENTITY values are positive and going up) and inserted via SET IDENTITY_INSERT <table_name> ON;. This approach brings consistency and reliability to the development / testing / release process.

On the production system, the Document_ID is a Varchar(100).

This is needlessly wasteful of space and CPU. Horribly ineffecient beyond using UNIQUEIDENTIFIER in the first place. No reason to use a string version of it. It is 16 bytes as UNIQUEIDENTIFIER but 36 bytes as VARCHAR (and always 36 so you might as well use CHAR(36)). The other issue with strings is that most people never consider Collation and how string comparisons are costly due to allowing for linguistic rules that are not needed here. If you must use a string, use a binary Collation:

CHAR(36) COLLATE Latin1_General_100_BIN2

After the paste, the GUID no longer works in queries, but only as a string:

Both of your examples are strings. Neither is using a true GUID. You supplied a string form of a GUID wrapped in notation indicating to some systems/protocols that the value is a GUID. Remove the curly brackets so that it is just:

Document_Id = 'B20DC300-DC3C-40D9-AC83-4756C96F83DC'
1
  • Thanks. Sorry, I can only select one Answer. I am sure you are right about "controlled manner" and rollout...and I'll keep those in mind as I continue refining my process.
    – Ken Lyle
    Jun 12 '17 at 13:54
1

It's hard to answer a question like this without seeing your exact steps. All I can say is that I haven't had that particular problem.

If I create a table and query a GUID:

CREATE TABLE Temp.T
(
Test    UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()
)
INSERT INTO Temp.T DEFAULT VALUES
SELECT * FROM Temp.T

...I get a an ANSI-standard* GUID:

44118913-5A4D-E711-82EC-3010B3F8365E

...and I can copy this into another query, as long as I quote it:

SELECT * FROM Temp.T WHERE Test = '44118913-5A4D-E711-82EC-3010B3F8365E'
INSERT INTO Temp.T (Test) VALUES ('44118913-5A4D-E711-82EC-3010B3F8365E')

It is possible that your configuration is different from mine in some respect. I don't know where your curly braces came from, for one thing. Did you type those in? If so, you do not need them; SQL Studio just wants the bare letters, numbers, and dashes.

* Not really; apparently GUIDs/UUIDs have a fairly complex pedigree.

0

Wow, Thanks for the education. All responses were correct - the fundamental issues were diagnosed correctly...curly braces do not a GUID make, etc.

I found that removing the curly braces from my linking table: Update Linked_Documents set Document_ID = Replace(REPLACE(document_id,'{',''),'}','') where Node_ID > 550000 solved my issue.

I would like to clarify that I am the current victim of our existing schema and import processes, not the architect. The use of SSMS and paste was a workaround by a departed dev for the extraneous NOT NULLs in our default schema. My intent here is to create a database centric process that humans can run using DB tools, not requiring a "developer" to run some fragile C# script from Visual Studio while shaking chicken bones at the sky.

This was my first post to SO - I really appreciate all the high quality answers and expertise offered so freely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.