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 ISNULL
/COALESCE
)? Also, why does this work fine without the PK but fails with it?
Best Answer
I was able to repro this.
Both plans had the row from
#temptable
driving a nested loops join.When
#DatabaseTable
has a primary key there is an index to seek into so the inside of the join has a seek. It is necessary to cast the varchar to uniqueidentifier to get the value to seek at which point the attempt to convert the invalid value fails.When there is no primary key the plan has a scan on the inside of the nested loops instead. it can defer the evaluation until it has a row to compare, there is no row output from the scan on
#DatabaseTable
(as it is empty) so theCONVERT_IMPLICIT
is never required to be evaluated.The position of the compute scalar may lead you to believe otherwise but see Compute Scalars, Expressions and Execution Plan Performance for more about that.