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: Happy CTASing and happy Friday!

Converting SQL_VARIANT on PDW

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog