has inline function got any catch?

Question

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

Answer 1

Hi @Rajesh Kumar Yadav

Scalar functions have some drawbacks: they are executed for each row of the result set, they prevent parallelism, and they can cause hidden conversions or errors.

For instance, avoid using scalar functions in WHERE clauses as they can cause scans and affect index efficiency. Inline table-valued functions are preferable over scalar functions, as they can be inlined and optimized by the query optimizer.

Sometimes, you may not need to use functions to achieve the same result. Computed columns can be used to define a column in a table calculated from other columns using a scalar expression, eliminating the need for scalar functions in SELECT statements or indexes. Alternatively, CROSS APPLY can be used to join a table with a table-valued function that takes a column from the table as a parameter, avoiding the need for scalar functions in JOIN conditions or expressions. Additionally, common table expressions (CTEs) can be used to define temporary result sets that can be reused within the same query, eliminating the need for table-valued functions in subqueries or joins.

What are the differences between scalar and table-valued functions in T-SQL?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I heard many people saying "functions are denounced in tsql" is there any catch?

Then ask those people "why".

Functions with data access can be trouble maker in meaning of performanse, because the UDF will be execute per row.

Answer 3

a function is a data transformation that removes any data cardinality statistics. in a result column it is not much of an issue, but in join or where clause this will effect query optimization. this is true of user defined and built in functions.