Converting SQL_VARIANT on PDW

There are a number of data types that are currently unsupported on PDW. Among those are SQL_VARIANT. The interesting part of that is that SQL_VARIANT along with several other unsupported data types are found all over the place in system tables in PDW. There is a simple solution, convert the value to something like VARCHAR. Well this wouldn’t be a blog post if that was all I had to say on the matter.
Turns out that you can select the data just fine, convert it in a select just fine, but for some odd reason not do an insert with it just fine. Let’s take the partitions range table for this example.
SELECT
CONVERT(VARCHAR(200), value, 101) AS ConvertedText
FROM sys.partition_range_values

Try to insert that into a temp table and bad things happen.
--Create a temp table
CREATE TABLE #Partitions
(
Value VARCHAR(200)
)
WITH (LOCATION = USER_DB)

--Load the temp table
INSERT INTO #Partitions
SELECT
CONVERT(VARCHAR(200), value, 101) AS ConvertedText
FROM sys.partition_range_values

--Select the records
SELECT * FROM #Partitions

--Drop the table
DROP TABLE #Partitions

You will get this error when it runs the insert statement even though it is clearly being converted.
[error]Msg 100077, Level 16, State 1, Line 1
Unsupported data type error. Statement references a data type that is unsupported in Parallel Data Warehouse, or there is an expression that yields an unsupported data type. Modify the statement and re-execute it.[/error]
The workaround: CTAS works like a charm!
CREATE TABLE TestPartition
WITH (DISTRIBUTION = REPLICATE) AS
SELECT
CONVERT(VARCHAR(200), value, 101) AS ConvertedText
FROM sys.partition_range_values

SELECT * FROM TestPartition
DROP TABLE TestPartition
For those who require visual proof:
CTAS_Partitions
Happy CTASing and happy Friday!
 

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Add comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me