Azure Data warehouse - User defined function issues

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):

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

Could you double check that function is created in DW?


Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =. Instead you must use DECLARE @var int = or SET @var =. SQL DW UDFs also do not yet support queries on user tables. Please use our feedback page to vote for new features.


There is a Data Warehouse Migration utility (available here) for Azure SQL Data Warehouse which picks up issues like incompatible datatypes, in-line functions, use of hints, use of RETURN statement, INSERT ... EXEC and many others:

Example Database Compatibility report

Unfortunately it does not pick up scalar functions which reference tables and really it should. However, for your particular function, it could just be a view (or even another table), eg

CREATE VIEW dbo.vw_ImpliedRates
AS
SELECT 1 term, [1] impliedRate, Multiple
FROM  dbo.ImpliedRate 
UNION ALL
SELECT 2 term, [2], Multiple
FROM  dbo.ImpliedRate 
UNION ALL
SELECT 3 term, [3], Multiple
FROM  dbo.ImpliedRate 
UNION ALL
SELECT 4 term, [4], Multiple
FROM  dbo.ImpliedRate;
GO

CREATE TABLE dbo.test
(
    Multiple    FLOAT NOT NULL,
    Term        INT NOT NULL
);
GO

INSERT INTO dbo.test ( Multiple, Term )
VALUES
    ( 0.001, 1 ), ( 0.001, 2 ), ( 0.001, 3 ), ( 0.001, 4 );
GO


SELECT impliedRate, v.Multiple
FROM dbo.test t
    INNER JOIN dbo.vw_ImpliedRates v
        ON t.Multiple = v.Multiple
       AND t.Term = v.Term;

SELECT *
FROM dbo.vw_ImpliedRates
WHERE Multiple = 0.001
  AND Term = 2

I tried this on my Azure SQL Data Warehouse and it worked perfectly well.

You should also know that scalar functions in SQL Server do not scale well when called against tables, and if you have the volume appropriate for Azure SQL Data Warehouse (ie billions of rows), then you will need to re-think use of scalar functions anyway. For example, using CTAS and writing more procedural code is a good approach that will allow you to make proper use of this immensely powerful platform.