has inline function got any catch?
-
hi,
I heard many people saying "functions are denounced in tsql" is there any catch?
q1) need to know what are the cases where we should not use?
q2) should we avoid scaler functions also?
q3) should we avoid scaler functions if we have to call it only once?
yours sincerley
- rajemessage 14195 wrote:
hi,
I heard many people saying "functions are denounced in tsql" is there any catch?
q1) need to know what are the cases where we should not use?
q2) should we avoid scaler functions also?
q3) should we avoid scaler functions if we have to call it only once?
yours sincerley
Not including "CLR" and "In-Memory OLTP" functions, there are 3 types of UDFs in SQL Server:
--===== The following both contain the word "BEGIN".
Scalar - should mostly avoid these like the plague
mTVF (multi-statement Table Valued Function) - Should mostly avoid these like the plague, as well.
--===== Does NOT contain the word "BEGIN"
iTVF (inline Table Valued Function) - Usually, incredible performance even when used as an iSF (inline Scalar Function).
Overall - People saying "functions are denounced in tsql" don't know what they're talking about because there are 3 types of UDF as identified above.
For Q1 - As previously stated, avoid Scalar and mTVF functions like the plague.
For Q2 - As previously stated, avoid Scalar and mTVF functions like the plague.
For Q3 - As previously stated, avoid Scalar and mTVF functions like the plague.
As previously stated, iTVFs are the way to go.
Read the multiple articles at the following link for more proof. Scalar functions suck even when they're not being used directly...
https://www.brentozar.com/?s=scalar+functions
Just in case there are another questions about Scalar UDFs in T-SQL... 😉
As previously stated, avoid Scalar and mTVF functions like the plague. Learn how to use iTVFs, instead.
Documentation for UDFs in T-SQL:
https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
There was a big improvement to the performance of scalar valued functions in SQL Server 2019.
But you need to make sure the improvement is switched on to get the benefit, this means setting the database compatibility level to at least 150 and:
SELECT * FROM sys.database_scoped_configurations;
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON; -
do u want to say that we should not use begin end in inlined scalar functions?
-
do u want to say that we should not use begin end in inlined scalar functions?
- rajemessage 14195 wrote:
do u want to say that we should not use begin end in inlined scalar functions?
Jeff's post says exactly what he meant to say. He did not condense his answer to the single-line radio-friendly snippet that you are hoping for because there is more that needed to be said on the subject. I suggest you read the content and links posted by Jeff and Jonathan.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
like inlinable scalar udfs requiremnts , is there any link which can tell me requiremnts of UDF like this one
- rajemessage 14195 wrote:
like inlinable scalar udfs requiremnts , is there any link which can tell me requiremnts of UDF like this one
- rajemessage 14195 wrote:
do u want to say that we should not use begin end in inlined scalar functions?
No.
- What I said was that Scalar and mTVF functions both use BEGIN/END in their definition. You cannot make either without the use of BEGIN/END.
- That means that, when you look at a function definition, if it contains BEGIN/END in the definition, it's either a Scalar or mTVF function, both of which make for some substantial performance issues.
So, what I'm saying is DO NOT USE EITHER Scalar or mTVF functions, PERIOD.
And the link I provided is a collection of all the reasons why I say not to use or create any function that contains BEGIN/END because that means that they're either Scalar or mTVF functions and you shouldn't be using either, PERIOD.
If you have existing Scalar functions, they "might" automatically "go inline"... I wouldn't count on it.
As for the 2019 stuff "being better", that doesn't prevent all of the problems that Scalar because those problems are also usually what will prevent a Scalar function from automatically inlining. It's definitely worth it to make the manual conversions to iTVFs and the code that uses them.
Again and as Phil said, if that's not the "single-line radio-friendly snippet that you are hoping for", here it comes...
Stop using Scalar UDFs, PERIOD.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply