Is there an alternative to a function returning Table type in Azure SQL Data Warehouse?

0
votes

In a regular SQL Server Database I a have function returning a table type as so:

CREATE FUNCTION [dbo].[GetSomeStuff] 
(   
    @UserID INT
)
RETURNS TABLE 
AS
RETURN 
(...

The function gets called from various stored procedures. I'd like to migrate the stored procedures along with this function to Azure SQL Data Warehouse, but TABLE types are not supported.

Can anyone tell me what I can do instead of a function returning a table type here?

1
azure-sqldw
I might be missing something but doesn't that use A TABLE type? In which case it isn't supported by azure data warehouse. Incidentally CTEs aren't either AFAIK. – Slicc

1 Answers

1
votes

You're correct, table types are not supported.

You can use CTEs, here's a simple query that runs on my DW:

with ProductSales as (
    select ProductID,
           sum(OrderQty) as UnitsSold 
    from SalesLT.SalesOrderDetail 
    group by ProductID
    ) 
select * 
from ProductSales 
order by UnitsSold desc;

That said, I often find CTEs to perform very badly in complex queries and prefer to materialise the CTE as a separate query so that I can control its structure and distribution.

I think materialisation is the right answer to your problem. Consider using a temporary table created in a stored procedure that replaces your function call. ASDW temporary tables are session scoped, and retained outside the procedure that created them.

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-temporary