March 26, 2009 at 2:48 pm
A function can return only one value. There is no reason that one value could not be a table when witting a new functions. I compared a very simple function that simple returns the integer passed in.
IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('dbo.InlineTableValuedFunction') AND TYPE = 'IF')
DROP FUNCTION dbo.InlineTableValuedFunction;
GO
CREATEFUNCTION dbo.InlineTableValuedFunction(@AnyValue int)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(SELECT @AnyValueAnyValue
);
GO
IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('dbo.UseDefinedFunction') AND TYPE = 'FN')
DROP FUNCTION dbo.UseDefinedFunction;
GO
CREATEFUNCTION dbo.UseDefinedFunction(@AnyValue int)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
RETURN @AnyValue;
END;
GO
Run as an Inline Table Valued Function.
DECLARE@StartTimeDATETIME = GETDATE();
DECLARE @WorkTable TABLE
(MyKeyINTIDENTITY PRIMARY KEY
,AnyValueINTNOT NULL
)
INSERT@WorkTable
(AnyValue
)
select FunctionAnswer.AnyValue
FROM AdventureWorks2008.Sales.SalesOrderDetailSalesOrderDetail
CROSS APPLY FunctionTest.dbo.InlineTableValuedFunction(SalesOrderDetail.SalesOrderID)FunctionAnswer
SELECT DATEDIFF(MS, @StartTime, GETDATE());
Run as a traditional UDF.
DECLARE@StartTimeDATETIME = GETDATE();
DECLARE @WorkTable TABLE
(MyKeyINTIDENTITY PRIMARY KEY
,AnyValueINTNOT NULL
)
INSERT@WorkTable
(AnyValue
)
select FunctionTest.dbo.UseDefinedFunction(SalesOrderDetail.SalesOrderID)
FROM AdventureWorks2008.Sales.SalesOrderDetailSalesOrderDetail
SELECT DATEDIFF(MS, @StartTime, GETDATE());
On my machne the Inline Table Function takes 156ms and the UDF takes 876ms. This is a huge difference.
Why would I ever write a traditional UDF with this kind of performance difference? or why is this comparison invalid?
Tom Groszko
March 26, 2009 at 3:02 pm
Assuming you CAN write an inline table-valued function, then yes - it probably makes sense to do so. The reason it gets so much faster is that it gets treated a bit like a view, in that the TEXT of the function gets inlined into the outer SQL, and then compiled and optimized as one single, larger statement. It essentially acts like a parameterized query for you.
Still - you will find that a lot of circumstances can't translate to one of those.
Note: this is true especially starting with 2005 (which I assume you are looking at given your calls to sys.objects, etc....). 2000 had a lot of challenges with functions which IMO made them hard to use at all.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 26, 2009 at 3:04 pm
All three are technically UDFs. Inline Table-Value (ITV), Multiselect Table Value (MTV) and Scalar (S) are all just types of UDFs.
Getting beyond the semantics, ITVs are limited to a single select statement, just like views. The difference is that a UDF can take input parameters, where a view can't. ITVs are generally much faster than MTVs, but they do have that limitation of a single select statement. Note that, within that select, they can have CTEs, derived tables, etc.
MTVs can do a LOT more than that. They can have conditional logic, they can have internal variables other than the parameters, they can manipulate data in multiple table variables, they can even have cursors in them. Most of the time, all that flexibility is actually more of a problem than a solution, because it also means that every execution plan that accesses one will assume it is working on just one row of data, and that can seriously kill performance. But, when/if you ever really need it, that flexibility can be quite a good thing. (I've only ever needed to use one, where a proc just wouldn't do what I needed, and it was quite useful that one time. That's in 9 years of database programming.)
So, if a UDF can be written as an inline, instead of a multi-value, definitely do so. Lots of advantages to it, not just speed. If it can't, then speed takes second place to necessary functionality.
But keep in mind, most of the time, all that necessary functionality can probably be done better through some other means.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply