If you can see the below query I have used scalar function multiple times in the query.When we used scalar function multiple times it will impact the overall execution of the query.
Sample Query:
How to avoid the above situation and try to remove the multiple uses of the scalar function. Same above query I have generated without using a scalar function.
Sample Query:
-- Scalar function Used Multiple Times in Query
SELECT
CASE WHEN [isValidEmailTableValue](Email) = 1 THEN 'Good Email Address'
WHEN [isValidEmailTableValue](Email) = 0 THEN 'Bad Email Address'
END As EmailOutput,
FROM CUSTOMER WITH (NOLOCK)
How to avoid the above situation and try to remove the multiple uses of the scalar function. Same above query I have generated without using a scalar function.
-- Avoid to use Same scalar function multiple times in Query
SELECT
CASE WHEN EmailFun.EmailValue = 1 THEN 'Good Email Address'
WHEN EmailFun.EmailValue = 0 THEN 'Bad Email Address'
END As EmailOutput,
FROM CUSTOMER WITH (NOLOCK)
CROSS APPLY [isValidEmailTableValue](Email) EmailFun -- Table Value Function Used as a Table
No comments:
Post a Comment