SQL Server 2019 Polybase External Table with UniqueIdentifier column

Scott Klein 161 Reputation points
2023-10-22T17:56:37.4366667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,901 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2023-10-22T18:12:13.2633333+00:00

    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)
    
    1 person found this answer helpful.
    0 comments No comments