SQL Server 2019 Polybase External Table with UniqueIdentifier column

Question

We are attempting to archive some old SQL Server data to Azure blob, and use PolyBase to query it. However, one of the columns is a uniqueidentifier data type, and when trying to create the external table, I get the error "The type uniqueidentifier is not supported with external tables".

CREATE EXTERNAL TABLE test

(

 ID integer NOT NULL,

col1 uniqueidentifier NOT NULL,

)

What are my options? Should I create the column as nvarchar(36)? What is the best workaround?

Thank you,

Scott

Answer 1

That would depend on how you store it in the blob. To use char(36) you would have to convert all them to strings.

I think it would be better to store them as-is, and use binary(16). You could then define a view top of the external table where you have

GUID AS cast(binaryguid AS uniqueidentifier)