SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UDF Help


UDF Help

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215314 Visits: 41979
Jeff Moden (2/27/2013)
TheSQLGuru (2/27/2013)
You may THINK you want to do a Scalar UDF, but you probably don't. I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is a very aptly named missive. Please do everything in your power to avoid the UDF and inline the code where you need it.

Oh, I haven't tested it, but I wonder if UDFs can call UDFs ...


Yes they can. if they're not iTVFs, it about as joyous as an aggregated view calling an aggregated view.


I guess that about sums up this thead. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19657 Visits: 7410
TheSQLGuru (2/27/2013)
. . .it about as joyous as an aggregated view calling an aggregated view.


You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product! w00t


No, those are "features", that add "flexibility" :-).

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215314 Visits: 41979
TheSQLGuru (2/27/2013)
. . .it about as joyous as an aggregated view calling an aggregated view.


You can do THAT TOO in SQL Server?!? Jeez, what a suck-@ss product! w00t


BWAAA-HAAAA!!! :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13406 Visits: 8001
Jeff Moden (2/25/2013)
Alan.B (2/25/2013)

If i am understand right. Function can return only one value. am i right?


Scalar functions return one value, table valued functions return a table variable.


Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.


I will confess, I have always used Scalar functions for returning a single value; I never thought to use an iTVF like that...

Unless I am misreading your comment - you are saying that scalar functions are worthless? Are there any cases where you would Scalar function over an iTVF that returns a single element?

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32273 Visits: 8674
Alan.B (2/28/2013)
Jeff Moden (2/25/2013)
Alan.B (2/25/2013)

If i am understand right. Function can return only one value. am i right?


Scalar functions return one value, table valued functions return a table variable.


Inline Table Valued Functions (iTVF for short) return a result set even if that result set is a single element. Think of it as an Inline Scalar Function. The "inline" type of function is about 7 times faster than any scalar function and also works faster for MultiLine Table Valued Fuctions.


I will confess, I have always used Scalar functions for returning a single value; I never thought to use an iTVF like that...

Unless I am misreading your comment - you are saying that scalar functions are worthless? Are there any cases where you would Scalar function over an iTVF that returns a single element?


I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table). However, if you use that scalar UDF directly in a SELECT/WHERE clause you can get totally hosed in several ways.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95016 Visits: 38963
Comes down to if the scalar function is being called once per query or once per row in the query.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32273 Visits: 8674
Lynn Pettis (2/28/2013)
Comes down to if the scalar function is being called once per query or once per row in the query.


There really is more to it than just the "cursor under the covers" effect.

1) It can lead to bad estimates and thus horribly bad plans (which can mean not only poor performance but also poor concurrency).

2) It WILL void the use of parallelism.

3) It can force the use of table spooling for halloween protection even when it isn't really required.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215314 Visits: 41979
TheSQLGuru (2/28/2013)

I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table).


Then you've got to read the following.
http://www.sqlservercentral.com/articles/T-SQL/91724/

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32273 Visits: 8674
Jeff Moden (3/1/2013)
TheSQLGuru (2/28/2013)

I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table).


Then you've got to read the following.
http://www.sqlservercentral.com/articles/T-SQL/91724/


1) on my laptop (powerful, with SQL 2012 SP1 CU2) sUDF and iTVF both run in EXACTLY the same time (143ms). I suppose the difference is a) your old machine and b) perhaps parallelism? My CTFP was set high enough so that the 2.x query cost of scanning that 1M row table and doing the calculation on the column didn't cause the iTVF to parallelize.

2) HOWEVER, your example was not what I was mentioning. I was speaking of the case of a SINGLE call to a sUDF to populate a variable that is used elsewhere and a SINGLE call to an iTVF to get same value into a temp object. Not calling each to make a calculation iteratively on a single large table.

I note that the two are not semantically equivalent and cannot be used identically later in the batch - I was just making an observation. Since it is confusing at best I will withdraw it to avoid further pursuit of a non-useful point. :-)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13406 Visits: 8001
This is something I need to play around with more. I took what Jeff said to imply that, in the code below, the iTVF function (nsq_iTVF) would be faster than the iSVF (nsq_iSVF).


CREATE FUNCTION dbo.nsq_iSVF (@int int)
RETURNS bigint WITH SCHEMABINDING
AS
BEGIN
RETURN @int/2
END
GO

CREATE FUNCTION dbo.nsq_iTVF (@int int)
RETURNS @rs TABLE (n bigint) WITH SCHEMABINDING
AS
BEGIN
INSERT @rs SELECT @int/2;
RETURN;
END
GO

--Create some test data
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp;

CREATE TABLE #tmp (n bigint);

INSERT #tmp
SELECT TOP 100000 ABS(CHECKSUM(NEWID()))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

SET NOCOUNT ON;
GO

--Test the Functions (on my local PC)
SET STATISTICS TIME ON
SELECT x.n n1, s.n n2
FROM #tmp x
CROSS APPLY dbo.nsq_iTVF(x.n) s
SET STATISTICS TIME OFF
GO

SET STATISTICS TIME ON
SELECT n n1, dbo.nsq_iSVF(n) n2
FROM #tmp x
SET STATISTICS TIME OFF
GO

DROP TABLE #tmp
GO



However, the iSVF makes short work of the iTVF.

 SQL Server Execution Times:
CPU time = 4977 ms, elapsed time = 5108 ms.

SQL Server Execution Times:
CPU time = 1497 ms, elapsed time = 1599 ms.


Doing the same test on a two functions I wrote - each correctly calculate the hamming distance between two strings... Again, the SVF remains undefeated.

Query:


--iTVF
CREATE FUNCTION dbo.hd_iTVF (@s1 varchar(8000), @s2 varchar(8000))
RETURNS @hd TABLE(hd int)
AS
BEGIN
WITH
nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally),
matrix AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1))
INSERT @hd
SELECT CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END
FROM matrix
WHERE s1<>s2;

RETURN;
END
GO

--iSVF
CREATE FUNCTION dbo.hd_SVF (@s1 varchar(8000), @s2 varchar(8000))
RETURNS int
AS
BEGIN
DECLARE @hd int=0;

WITH
nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM [master].dbo.spt_values Tally),
matrix AS (SELECT SUBSTRING(@s1,n,1) s1, SUBSTRING(@s2,n,1) s2 FROM nums WHERE n<=LEN(@s1))
SELECT @hd=CASE WHEN LEN(@s1)<>LEN(@s2) THEN NULL ELSE COUNT(*) END
FROM matrix
WHERE s1<>s2;

RETURN @hd;
END
GO

--Create some test data
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp;

CREATE TABLE #tmp (s1 varchar(10), s2 varchar(10))

INSERT #tmp
SELECT TOP 100000 s1 = ABS(CHECKSUM(NEWID())), s2=ABS(CHECKSUM(NEWID()))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;

SET NOCOUNT ON;
GO

--Test the Functions (on my local PC)
SET STATISTICS TIME ON
SELECT s1, s2, s.hd
FROM #tmp x
CROSS APPLY dbo.hd_iTVF(x.s1,x.s2) s
SET STATISTICS TIME OFF
GO

SET STATISTICS TIME ON
SELECT s1, s2, dbo.hd_SVF(s1,s2) AS hd
FROM #tmp x
SET STATISTICS TIME OFF
GO

DROP TABLE #tmp
GO



Results:

SQL Server Execution Times:
CPU time = 4977 ms, elapsed time = 5108 ms.

SQL Server Execution Times:
CPU time = 1497 ms, elapsed time = 1599 ms.



Yes, I have begun reading Jeff's Spackle article about this (great article as usual) and am at the TEST, TEST, TEST part.

Based on my testing here - this is a case where a SVF (inline or otherwise) is superior. Is this because I need to do a cross join to get my scalar value from the iTVF?

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search