No Implicit Conversion Between UNIQUEIDENTIFIER and VARCHAR After Removing a Persisted Computed Column

I have a couple of tables in a database where the primary key (stored as a UNIQUEIDENTIFIER) of one table is stored as a column in the other table as VARCHAR representation of the UNIQUEIDENTIFIER in the first table.

I added persisted computed column to the second table, so that it would store the VARCHAR value (if it was present) as a UNIQUEIDENTIFIER.

I did this by using the following formula for the persisted computed column:

SELECT CASE 
        WHEN isnull(ref_id, '') <> ''
            THEN cast(ref_id AS UNIQUEIDENTIFIER)
        ELSE NULL
        END

It turns out that I don't need the persisted computed column, so I deleted it in SSMS.

The problem is that now when I want to join on the two columns, I get this error message:


Conversion failed when converting from a character string to
uniqueidentifier.

This is a result of a query like this:

SELECT *
FROM mngi_referral_ r
INNER JOIN mngi_ppa_ext_ ppa ON r.seq_no = ppa.ref_id

Previous to adding the computed column, I could join on these two columns (r.seq_no is a UNIQUEIDENTIFIER and ppa.ref_id is a VARCHAR). SQL Server simply did an implicit conversion and never complained.

I can still do this in other tables in the database without a problem. It seems to only be specific to this specific column ppa.ref_id) since I added and then subsequently deleted the computed persisted column.

Does anyone have any idea why this would be the case? I would greatly appreciate any insight you can offer.

Here is the table creation script for the table in question:

```
CREATE TABLE [dbo].mngi_PPA_ext_ NOT NULL,
[practice_id] char NOT NULL,
[person_id] [uniqueidentifier] NOT NULL,
[created_by] [int] NOT NULL,
[create_timestamp] [datetime] NOT NULL,
[create_timestamp_tz] [smallint] NULL,
[modified_by] [int] NOT NULL,
[modify_timestamp] [datetime] NOT NULL,
[modify_timestamp_tz] [smallint] NULL,
[row_timestamp] [timestamp] NO

Blank values in the ref_id column will result in the query returning your error message.

DROP TABLE IF EXISTS dbo.a;
CREATE TABLE dbo.a 
(
    i uniqueidentifier null
);

DROP TABLE IF EXISTS dbo.b;
CREATE TABLE dbo.b
(
    i varchar(36) null
);

INSERT INTO dbo.a (i)
VALUES (NEWID());

INSERT INTO dbo.b (i)
VALUES ('');

SELECT *
FROM dbo.a 
    INNER JOIN dbo.b ON a.i = b.i;

Results:


Msg 8169, Level 16, State 2, Line 4

Conversion failed when converting from a character string to uniqueidentifier.

This serves as a pretty good example of why it is important to choose the correct data type for each and every column. uniqueidentifier columns don't accept zero-length-strings in the first place. You can insert NULL values, if you really need to.

This is the query plan, shown in Sentry One's Plan Explorer for the above sample query:

At the bottom of the image is a nice red warning about cardinality estimates being unreliable because of the implicit conversion required by joining a varchar(36) column to a uniqueidentifier column. Any time you see this warning in a plan, fix the issue before it gets out of hand. Great cardinality estimates are absolutely required for good performance. Without them, SQL Server has almost no hope of creating a great query plan.

StackExchange Database Administrators Q#204551, answer score: 3