3

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_](
    [enterprise_id] [char](5) NOT NULL,
    [practice_id] [char](4) 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] NOT NULL,
    [seq_no] [uniqueidentifier] NOT NULL,
    [txt_proc] [varchar](150) NULL,
    [txt_date] [varchar](10) NULL,
    [txt_ref_status] [varchar](25) NULL,
    [txt_ref_source] [varchar](25) NULL,
    [txt_task_status] [varchar](25) NULL,
    [txt_mngi_loc] [varchar](75) NULL,
    [txt_mngi_provider] [varchar](75) NULL,
    [txt_new_update] [varchar](6) NULL,
    [txt_referral_md] [varchar](125) NULL,
    [txt_referral_link] [varchar](125) NULL,
    [txt_ref_md_firstname] [varchar](75) NULL,
    [txt_ref_md_lastname] [varchar](75) NULL,
    [txt_ref_md_provID] [varchar](40) NULL,
    [ref_id] [varchar](36) NULL,
    [txt_ref_phone] [varchar](10) NULL,
    [txt_interpreter] [varchar](100) NULL,
    [txt_diagnosis] [varchar](500) NULL,
    [txt_mngi_md_firstname] [varchar](75) NULL,
    [txt_mngi_md_lastname] [varchar](75) NULL,
    [txt_mngi_prov_id] [varchar](40) NULL,
    [chk_intrp] [int] NULL,
    [rbtn_sedation] [varchar](1) NULL,
    [txt_sedation_orderingMD] [varchar](125) NULL,
    [txt_ordering_MD_fname] [varchar](75) NULL,
    [txt_ordering_MD_lname] [varchar](75) NULL,
    [rbtn_lung] [varchar](1) NULL,
    [rbtn_allergicToAnes] [varchar](1) NULL,
    [txt_height_ft] [int] NULL,
    [txt_height_in] [int] NULL,
    [txt_weight] [int] NULL,
    [txt_BMI] [decimal](16, 2) NULL,
    [txt_height] [int] NULL,
    [txt_temp_proc] [varchar](55) NULL,
    [chk_send_task] [int] NULL,
    [pt_sx] [varchar](1) NULL,
    [rbtn_sendtask] [varchar](1) NULL,
    [mngi_ppa] [varchar](15) NULL,
    [chk_hide] [int] NULL,
    [contactDetailID] [varchar](36) NULL,
    [txt_procNameFull] [varchar](150) NULL,
    [txt_proc_assess_needed] [varchar](3) NULL,
    [txt_proc_assess_status] [varchar](15) NULL,
    [txt_proc_prep] [varchar](25) NULL,
    [txt_proc_seq_no] [varchar](36) NULL,
    [txt_change_reason] [varchar](50) NULL,
    [rbtn_changeReason] [varchar](3) NULL,
    [chk_uploadContactDtl] [int] NULL,
    [appt_id] [varchar](36) NULL,
    [referring_clinic_id] [varchar](36) NULL,
    [health_system_id] [varchar](36) NULL,
    [ApplicationUserId] [varchar](36) NULL,
    [chk_prepUpdatedManually] [int] NULL,
    [ReferringComments] [varchar](300) NULL,
    [ReferringCommentsSummary] [varchar](50) NULL,
    [completedDate] [varchar](10) NULL,
    [completedTime] [varchar](80) NULL,
    [txt_appt_date] [varchar](10) NULL,
    [txt_appt_rendering] [varchar](100) NULL,
 CONSTRAINT [pk_mngi_ppa_ext_] PRIMARY KEY NONCLUSTERED 
(
    [seq_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [NEXTGEN_INDEX_1]
) ON [NEXTGEN_CORE]
GO

ALTER TABLE [dbo].[mngi_PPA_ext_] ADD  DEFAULT (getdate()) FOR [create_timestamp]
GO

ALTER TABLE [dbo].[mngi_PPA_ext_] ADD  DEFAULT (getdate()) FOR [modify_timestamp]
GO

I've tried recompiling the table with sp_recompile to no avail.

There are not only NULL values, but also blank values in the column, along with valid VARCHAR representations of GUIDs.

  • I know I'm not supposed to, but I just wanted to say thank you to all who gave feed back and help here. Much appreciated! – Bill Kron Apr 20 '18 at 18:39
3

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:

enter image description here

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.

  • 1
    Ugh. No, you're totally right. I made a bad assumption. Mea culpa. I know which badge I've earned today. :-) – Bill Kron Apr 20 '18 at 18:26
  • I completely agree with you on this point, of course. I am working with a medical records system application that allows you to customize things. However, you're limited to their toolset. One thing you can't do is create columns with the UNIQUEIDENTIFIER type. So, we have this problem ALL OVER the database. The bottom line is that I don't have complete control over the tables that get created. Adding the persisted computed column was a desperate attempt to relieve a performance issue I've been wrestling with (this was all done in development of course). Thank you for hanging in there with me! – Bill Kron Apr 20 '18 at 18:38
  • My pleasure, Bill. I would raise the issue with the developer team for the product - they will have nothing but trouble with the product like that. If you have the choice to use integers perhaps that would work well if you don't actually require uniqueidentifiers. – Max Vernon Apr 20 '18 at 18:40
  • 1
    @BillKron Just to be sure you're aware, a computed column can be indexed without being persisted. Very common misconception that they're tied together. – Aaron Bertrand Apr 20 '18 at 18:41
  • @AaronBertrand - This is most helpful to know--thank you! – Bill Kron Apr 20 '18 at 18:42

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.