So we were recently running some logic tests on an insert into a table with a
uniqueidentifier as a primary key. Here's my test query;
IF OBJECT_ID('tempdb..#DatabaseTable') IS NOT NULL DROP TABLE #DatabaseTable CREATE TABLE #DatabaseTable ([ID] [uniqueidentifier] NOT NULL PRIMARY KEY, [LastUpdate] [datetime] NOT NULL, [Locked] [tinyint] NOT NULL) IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable CREATE TABLE #temptable (ID varchar(36), Data nvarchar(max)) INSERT INTO #temptable (ID, Data) VALUES ('g078f19e-e150-4bb9-b5f4-b20b3fc64016', 'text goes here') SELECT tmp.ID ,tmp.Data FROM #temptable tmp LEFT JOIN #DatabaseTable db ON tmp.ID = db.ID WHERE db.ID is null
This fails because the uniqueidentifier is not valid as it contains an incorrect value of 'g' that sits outside the acceptable range for this data type. This happened as a developer had altered it manually for testing as he knew that it wouldn't exist in the destination table but wasn't aware of the valid character list.
This is the error that it throws;
(1 row(s) affected) Msg 8169, Level 16, State 2, Line 13 Conversion failed when converting from a character string to uniqueidentifier.
This is the behaviour that I would expect. However, when messing about in testing I removed the primary key on #DatabaseTable and it runs totally fine. Now I examined the execution plan expecting it to convert the uniqueidentifier to match the
varchar(36), however I see this compute scalar;
CONVERT_IMPLICIT(uniqueidentifier,[tempdb].[dbo].[#temptable].[ID] as [tmp].[ID],0)
I can't seem to find any documentation from Microsoft on the actual syntax of CONVERT_IMPLICIT so I am assuming that the final zero is what it is replaced with if the convert fails, would this be a correct assumption (same as
COALESCE)? Also, why does this work fine without the PK but fails with it?