SQL Server - Convert scalar function into table valued function

Question

I have below scalar function:

CREATE FUNCTION [dbo].[GetStringIds]
(
   @IdsTbl ids READONLY  
)
RETURNS nvarchar(800)
AS
BEGIN   
    DECLARE @IdsLst nvarchar(800);

    SELECT 
           @IdsLst = COALESCE(@IdsLst + ', ', '') + '(' + CAST(ID1 AS NVARCHAR) + ' : ' + CAST(ID2 AS NVARCHAR) + ')'
      FROM   
           @IdsTbl 

    Return @IdsLst 
END

A table type is passed to this scalar function which contains two fields ID1 and ID2, both int type. This function builds an strings like below:

(12 : 2), (23 : 4), (4 : 34)

NOTE: ID1 can be repeated but not ID2, ID1+ID2 are primary keys. For example:

Now, I am wondering if it is better to convert it to a table valued function in order to increase database performance in terms of execution time.

So in this case, if I convert it to a table valued function, will it increase performance? Is its execution time be reduced? If so how can convert it to a table valued function? I have problems to convert it as I need to declare a variable in which concatenate.

What table you want to return? I mean, what will be it's columns? — Zohar Peled, Jan 25 '17 at 12:40
In fact, you don't need to declare variable in order to concatenate strings, since there are ways to concatenate data from query without variable. For example http://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings — Andrey Korneyev, Jan 25 '17 at 12:41
@ZoharPeled the table I want to return is always one row with one column and this column is a nvarchar (the concatenation result). — Willy, Jan 25 '17 at 12:59
@ZoharPeled because I thought that using a table value function is better in performance than scalar function. In fact is what in most post is said. — Willy, Jan 25 '17 at 14:04

score 0 · Answer 1 · answered Jan 25 '17 at 12:58

0

I usually use construction like that:

select 
    (select '' + '(' + CAST(ID1 AS NVARCHAR) + ' : ' + CAST(ID2 AS NVARCHAR) + ')'
        from @IdsTbl
        for xml path ('')
    )

answered Jan 25 '17 at 12:58

xdd

535
2
4

SqlZim · Answer 2 · 2017-01-25T13:11:03.287

0

create function dbo.GetStringIds (@IdsTbl dbo.ids readonly) 
  returns table as return
  select IdsLst = stuff(
    ( select ', (' + cast(id1 as nvarchar(13)) 
           + ' : ' + cast(id2 as nvarchar(13)) + ')'
      from @IdsTbl i
      for xml path (''), type).value('.','nvarchar(800)')
    ,1,2,'');

Inline Scalar Functions - Itzik Ben-Gan

edited Jan 25 '17 at 13:11

answered Jan 25 '17 at 13:02

SqlZim

37,248
6
41
59

score 0 · Answer 3 · answered Dec 08 '17 at 12:18

You can do something like this,

CREATE FUNCTION [dbo].[GetStringIds] (@IdsTbl ids READONLY)
Returns Table
Return (
     SELECT 
       @IdsLst = COALESCE(@IdsLst + ', ', '') + '(' + CAST(ID1 AS NVARCHAR) + ' : ' + CAST(ID2 AS NVARCHAR) + ')'
  FROM   
       @IdsTbl 
)

This may work as my requirement was different I tried to edit in that. Hope for the best.

SQL Server - Convert scalar function into table valued function

3 Answers3