Type Conversion in Persisted Computed Column

Question

Hi everybody

I'm working with 2 related tables in a Microsoft SQL Server 2008 environment which are connected via a GUID. In one table, the field has the type varchar(50), the other one is properly types as uniqueidentifier. This is obviously bad but I can't change this now because it's given by a legacy piece of software.

The conversion SQL Server needs to perform at each inner join makes queries running terribly slow, since I can't use indices at all. I tried adding a Computed Column, which is persisted, to get the ID stored as a uniqueidentifer. This way I could add an index to get it running much faster probably. I failed.

Does anybody know if I can store an explicitly converted value in a computer column. If I can, what's the formula to use here?

Cheers, Matthias

Answer 1

This worked for me:

CREATE TABLE t_uuid (charid VARCHAR(50) NOT NULL, uuid AS CAST(charid AS UNIQUEIDENTIFIER))

CREATE INDEX IX_uuid_uuid ON t_uuid (uuid)

INSERT
INTO    t_uuid (charid)
VALUES  (NEWID())

SELECT  *
FROM    t_uuid

Answer 2

CONVERT(uniqueidentifier, your_varchar_here)

Answer 3

Depending on how often you need to make the conversion for joining, I'd use a CTE to convert the data type(s). It is constructed faster than an inline view (next best temporary option). In either case, you'd expose value as the correct data type in a result column from the CTE/inline view so you can JOIN on to it. CTE Example:

WITH example AS (
   SELECT t.guid
          CONVERT(UniqueIdentifier, t.guid) 'cguid'
     FROM TABLE t)
SELECT t.*
  FROM TABLE t
  JOIN example e ON e.cguid = t.guid

Inline view example:

SELECT t.*
  FROM TABLE t
  JOIN (SELECT t.guid
               CONVERT(UniqueIdentifier, t.guid) 'cguid'
          FROM TABLE t) e ON e.cguid = t.guid

It's not going to get around that the index for guid (assuming one does) won't be used, but it's also not a good habit to be performing data type conversion in the WHERE clause.