Join table in Select Statement on UNIQUE ID to CHAR

Transact-SQL
1

I need to join a table on columns with two different properties
TABLE_A.ColumnB CHAR(35) join TABLE_B.ColumnB uniqueidentifier

Can i do this just in a select statement only without creating a temporary able?

If I used CASE (Table_B.ColumnB AS CHAR(35)) , would this work and if so, how would i fit that into my select statements? I need to pull 2 different data points into my report that are on Table B and all the other data is on Table A.

Thanks for any help!

2

hi

please see if this link helps :slight_smile:
while joining you will have to convert unique identifier to varchar
and then join
and also the length of the varchar matters

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/189561d2-2b25-49e2-bd18-6560b232ee00/how-to-convert-uniqueidentifier-to-varchar-in-sql?forum=sqlgetstarted

3

I also state that such a join will be non SARGable (SARG = Search ARGument) and an index seek will be impossible because of the conversion. I also agree with @harishgg1 about the length of the CHAR() based column. Normally when you convert a UniqueIdentifier to a CHAR(), it requires 36 characters and not just 35. That likely means that your CHAR(35) will need the insertion of an extra character.

Can you post a couple of examples of what you have in the CHAR(35) column so we can advise better?

4

I was reading a database table directory to come up with the (35); it may be wrong.

Here is some examples:
Table A - Some may be NULL as well but the column is called a.refer_to_prov_id:
'B9AA3DB8-F35C-4D19-B730-D7B7E1772783' , '28C960F0-EDED-41E4-9EF0-0487CD5D6694'

Table B - there will never be a Null Value and the column is b.provider_id - the returned value will always be the same as what can be found in table A.

5

IF the data in table A is clean, just compare them, SQL will automatically take care of the needed conversion:

{ON / WHERE} TABLE_A.ColumnB = TABLE_B.ColumnB

If there's a decent chance that the data in TABLE_A.ColumnB will not be valid, that is not a valid uid value, then you will have to cast the other column as varchar:

{ON / WHERE} TABLE_A.ColumnB = CAST(TABLE_B.ColumnB AS varchar(36))

1 Like
6

Just to confirm, those two examples are the correct rendition for a character-based equivalent of a UNIQUEIDENTIFIER and they are 36 characters long.

An implicit cast will work, so your join can be done using just the names of the columns. Here's a bit of proof that the implicit cast will work.

--===== Prove that an implicit cast between a UNIQUEINDENTIFIER and an
     -- identical CHAR(36) value will work as expected.
DECLARE  @GUID UNIQUEIDENTIFIER = '28C960F0-EDED-41E4-9EF0-0487CD5D6694'
        ,@CHAR CHAR(36)         = '28C960F0-EDED-41E4-9EF0-0487CD5D6694'
;
--===== Test for identical values
 SELECT CASE
        WHEN @GUID = @CHAR THEN 'Identical'
        ELSE 'Not Identical'
        END
;
 SELECT CASE
        WHEN @GUID <> @CHAR THEN 'Not Identical'
        ELSE 'Identical'
        END
;
--===== Test for non-identical values
 SELECT @CHAR = '28C960F0-EDED-41E4-9EF0-0487CD5D6695' --Changed last digit by 1
;
 SELECT CASE
        WHEN @GUID = @CHAR THEN 'Identical'
        ELSE 'Not Identical'
        END
;
 SELECT CASE
        WHEN @GUID <> @CHAR THEN 'Not Identical'
        ELSE 'Identical'
        END
;

As I said though, implicit casts destroy SARGability. According to the "Data Type Precedence" rules of SQL Server (found at the following link), SQL Server will implicitly convert the CHAR(36) column of data to the UNIQUEIDENTIFIER data type BEFORE the join works and that also means that an INDEX SEEK will never be done on the CHAR(36) column. It will always be a SCAN of some type and that can really slow a query down and will certainly use a lot more resources behind the scenes.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

You can fix that problem in one of two ways...

  1. Change the CHAR(36) column to a UNIQUEIDENTIFIER datatype (which may break code but IS, IMHO, the best solution).
  2. Add a PERSISTED compute column to Table A that does the conversion for you and join to that, instead. The PERSISTED column IS indexable. Be aware that it is also materialized on INSERT and so becomes a part of the underlying space requirements for the table and any related indexes. It may well be worth that tradeoff, though.
1 Like