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.
Best Answer
Blank values in the
ref_id
column will result in the query returning your error message.Results:
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.