CODEWITHSUNDEEP
sqlsql-servert-sqluser-defined-functions
Anthony Faull
Anthony Faull

Reputation: 17987

Inline scalar functions: real or vaporware?

What is the correct syntax to create an inline scalar function in SQL Server?

Books Online, in the Types of Functions chapter (2005 and up), talks about Inline Scalar Functions as if they exist and as if no BEGIN...END block is required (in contrast with multiline functions):

For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value.

I also noticed a row for "IS: inline scalar function" in the list of object types in the spt_values table:

SELECT name
FROM master..spt_values
WHERE type = 'O9T'
AND name LIKE '%function%'

I have tried to create such a function with no success:

CREATE FUNCTION AddOne(@n int) RETURNS int
AS
    RETURN @n + 1

The error message is

Msg 102, Level 15, State 31, Procedure AddOne, Line 3 Incorrect syntax near 'RETURN'.

Am I missing something or is there an error in Books Online?

Upvotes: 20

Views: 8136

Answers (4)

Karthik
Karthik

Reputation: 106

Starting with SQL Server 2019 CTP2.1, there is a new feature called "Scalar UDF inlining" which can automatically inline scalar UDFs when certain preconditions are satisfied.

The official blog post introducing the feature is here: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/introducing-scalar-udf-inlining/

Detailed documentation that describes the feature is here: https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=azuresqldb-current

Upvotes: 4

SqlNightOwl
SqlNightOwl

Reputation: 43

Correct, there is no such thing as a inline scalar function. One can be "simulated" by using an inline-TVF, however, the syntax of any "clients" will need to change. 1) create he function:

    create function dbo.AddOne(@input int) returns table as return (select @input + 1 as value);

2) in the "client" code, do this...

    (select value from dbo.AddOne(Column)) as ColumnPlusOne

An now you have a functioning inline scalar function.

I've had to do this to replace a lot of scalar UDFs in my client code that looked like this...

create function dbo.GetLookupID(@code varchar(50)) returns int
as
begin
    declare @return int;
    select @return = LookupID from dbo.Lookups where Code = @code;
    return @return;
end;

I tried fixing it by removing the variable...

create function dbo.GetLookupID(@code varchar(50)) returns int
as
begin
    return (select LookupID from dbo.Lookups where Code = @code);
end;

That was an improvement, however, there was still an unpleasant performance hit. When I changed to the iTVF and changed the calling convention.... it got much better.

Upvotes: 4

RBarryYoung
RBarryYoung

Reputation: 56755

Well, AFAIK, none exist (not even in the hidden [mssqlsystemresource] database) and there's no syntax to create one. So it appears that this is something that Microsoft must have anticipated in the run-up to SQL Server 2005 by adding a type for it (and doc!), but never actually implemented for some reason.

Though it is one of the single most requested features for all of Ms Sql Server. Primarily because the default UDF's are so slow and we end up having to back-end ITVF's to get the same effect. (difficult and clumsy, but it works).

Upvotes: 11

Ian Horwill
Ian Horwill

Reputation: 3025

I'm seeing the same thing. That sentence seems to be the only reference to "inline scalar functions". This article claims that inline table-valued functions can be fast enough to do the job.

Upvotes: 1

Related Questions