How can we improve Microsoft Azure SQL Data Warehouse ?

Remove Scalar Function limitiations

Currently we can not do the following:

use Global variables within UDF, which makes us to send it from the front end as a value. eg; @@DateFirst
reference tables within the UDF
reference temp tables within a UDF

15 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Siddhartha Mohapatra shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

10 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
An error occurred while saving the comment
  • Siddhartha Mohapatra commented  ·   ·  Flag as inappropriate

    We can not use # tables inside UDF where as we can use it within Stored procs. It is definitely a needed feature while writing complex functions.

  • Raj commented  ·   ·  Flag as inappropriate

    We are facing the similar issue. Do we have any updates on this?

  • Alex C. commented  ·   ·  Flag as inappropriate

    Going the stored procedure way does not work out....
    SET @variable = exec sproc @input 'parameter1' does not work....

    This means running calculations with functions/sprocs which access table data is not possible at all at this moment. An official statement on the product roadmap on whether this feature will be introduced or not would be very appreciated.

  • Arshad Ali commented  ·   ·  Flag as inappropriate

    We are porting from SMP SQL Server where we have a UDF which works fine but when we run the same function in SQL DW it always returns NULL value. I am providing complete script for you to test it at your end.

    --Create a configuration table and load some data to it
    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_Globals]') AND type in (N'U'))
    DROP TABLE [dbo].[_Globals]
    GO
    CREATE TABLE [dbo].[_Globals](
    [Parameter] [nvarchar](50) NOT NULL,
    [Value] [nvarchar](255) NULL,
    [Description] [nvarchar](200) NULL
    ) WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUND_ROBIN);
    GO
    INSERT [dbo].[_Globals] ([Parameter], [Value], [Description]) VALUES (N'language1', N'E', N'Primary language. Required')
    GO
    INSERT [dbo].[_Globals] ([Parameter], [Value], [Description]) VALUES (N'language2', N'N', N'Secondary languageq. Required')
    GO

    --Query the configuration table and it should return result as expected
    DECLARE @value varchar(500)
    DECLARE @result varchar(100);
    SET @value = 'language2'
    SET @result = (SELECT Value FROM [dbo].[_Globals] WHERE [Parameter] = @value)
    select @result

    --Create a function
    DROP FUNCTION dbo.get_param
    go
    CREATE FUNCTION dbo.get_param (@value varchar(50))
    RETURNS varchar(255)
    AS
    BEGIN
    DECLARE @result varchar(255);
    SET @result = (SELECT Value FROM [dbo].[_Globals] WHERE [Parameter] = @value)
    RETURN(@result);
    END;
    GO
    --When we query the function it always returns NULL if we use SELECT command to query data
    --from some other table based on parameter passed to function
    SELECT [dbo].[get_param] ('language2')

  • JRJ commented  ·   ·  Flag as inappropriate

    Hi Alex,

    Thank you for your improvement suggestion. As you point out there is currently a difference between scalar function support in SQL DW and SQL Server. I did want to let you know that this is a known behavior and is a limitation of our implementation today. We document this in the CREATE FUNCTION msdn article for SQL DW here https://msdn.microsoft.com/en-US/library/mt203952.aspx as follows:

    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.

    I will ask for this to be made clearer. Thanks again for taking the time to submit your improvement suggestion.

  • Alex C. commented  ·   ·  Flag as inappropriate

    Of course the Configuration table contains values. And of course it contains one value with the parameter "parameter1"...

  • Alex C. commented  ·   ·  Flag as inappropriate

    CREATE TABLE [dbo].[Configuration] (
    [Parameter] nvarchar(50) NOT NULL,
    [Value] nvarchar(255) NULL,
    [Description] nvarchar(200) NULL
    )
    WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUND_ROBIN);

    CREATE FUNCTION [dbo].[get_param] (@value nvarchar(255))
    RETURNS nvarchar(255)
    AS
    BEGIN
    RETURN
    (SELECT TOP 1
    g.Value as v
    FROM
    dbo.Configuration as g
    WHERE
    g.Parameter = N'currency')
    END

    SELECT dbo.get_param('parameter1')

Feedback and Knowledge Base