SQL Data Warehouse - unale to create table-valued functions

Question

Background:

I am migrating a SQL Server datawarehouse to Azure SQL Data Warehouse. The database is the source for an SSAS OLAP cube.

Some of the measure groups have been partitioned and rely on a table-valued function to query the relevant data for the partition.

Issue:

I'm not able to create these functions in Azure-DWH.

Sample function: create function udm.MyFunction () returns table as return ( select getdate(), getutcdate() )

Error Message: Msg 104051, Level 16, State 1, Line 46 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.

The MSDN link: https://msdn.microsoft.com/en-US/library/mt203952.aspx lists the "return_data_type" and mentions "Is the return value of a scalar user-defined function. For Transact-SQL functions, all scalar data types supported in SQL Data Warehouse are allowed. The timestamp (rowversion) data type is not a supported type."

My assumption is the TABLE return type is the error as I've been able to create functions returning a single value.

I'm unsure if the table-values functions are supported or not, and if so what I need to do to get them working.

If the cube is Multidimensional then I see why you were using functions. If it is Tabular then you should be able to use stored procedures. Which is it? — GregGalloway
It is Multidimensional & is SQL Server 2016 hosted in an Azure VM. — BenOnline

JRJ JRJ · Accepted Answer · 2016-07-19T11:29:09

Azure SQL Data Warehouse does not currently support inline or multi-statement table valued functions. At this time only scalar functions. There is also one additional restriction compared to SQL Server. The scalar function body can not reference a table. Please refer to the function body section for further details:

function_body (msdn)

Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.

For your situation you will need to use an alternative method; either a proc with some additional metadata or explicit queries.

SQL Data Warehouse - unale to create table-valued functions

2 Answers