Performance of T-Sql query inside scalar user-defined function

Question

I recently reviewed a colleague's SQL Server function (in SQL Server 2008) where he had put a T-SQL query within a scalar user-defined function. That function was then used in the Select clause of a query.

I thought that having a query inside a function would have terrible performance because I assumed each row returned would have to run the query - even though that query is optimised to query on an index.

Here's an example of what I'm talking about:

create table [PERSON] (
ID int primary key,
FIRSTNAME NVARCHAR(100),
MIDDLENAME NVARCHAR(100) null,
LASTNAME NVARCHAR(100))
GO

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (1, 'BOB', 'M', 'BLUE')

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (2, 'VALERIE', 'J', 'GREEN')

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (3, 'SIMON', 'D', 'RED')

INSERT INTO PERSON (ID, FIRSTNAME, MIDDLENAME, LASTNAME)
VALUES (4, 'LIONEL', 'W', 'BROWN')
GO

-- Scalar Function with T-SQL
CREATE FUNCTION dbo.fn_FormatNameFromId(
            @pnPersonId int
            )
Returns nvarchar(300)
AS
Begin
    return (Select FIRSTNAME + ' ' + MIDDLENAME + ' ' + LASTNAME
        From PERSON
        Where ID = @pnPersonId)
End
go

-- Scalar Function without T-SQL
CREATE FUNCTION dbo.fn_FormatNameFromValues(
            @psFirstName nvarchar(100),
            @psMiddleName nvarchar(100),
            @psLastName nvarchar(100)
            )
Returns nvarchar(300)
AS
Begin
    return (@psFirstName + ' ' + @psMiddleName + ' ' + @psLastName)
End
go

-- T-SQL within function
select dbo.fn_FormatNameFromId(ID)
from PERSON

-- Pass values directly
select dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME)
FROM PERSON

When comparing the execution plans for both these selects, I could find no difference in the cpu or memory performance of the two functions. Even when scaling up the test to return tens of thousands of rows, there was no performance difference.

Can anybody explain how or why the fn_FormatNameFromId is not causing a loss of performance?

Answer 1

Personally i am not a geat fan of t-sql functions as far as PERFORMANCE is concern so i usally avoid them as much as possible in SQL SERVER,

YES i know there are many many advantages of user functions in programming.

lets go to your questions directly

When comparing the execution plans for both these selects, I could find no difference in the cpu or memory performance of the two functions

i usually dont compare executions plan to check which plan is better and which is worse on the bases of subtree/cpu costs because these costs are just based on estimated values.

Can anybody explain how or why the fn_FormatNameFromId is not causing a loss of performance?

because you are still dealing with little data i tested your both function like below:

declare @a int=1
Declare @name varchar(50)
while (@a<99999)
Begin 
-- T-SQL within function
select @name = dbo.fn_FormatNameFromId(ID)
from PERSON
SET @a =@a+1
END

it ran on my local system in 13 second

declare @a int=1
Declare @name varchar(50)
while (@a<99999)
Begin 

-- Pass values directly
select @name =dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME)
FROM PERSON
SET @a =@a+1
END

it ran on my local system in 9 secound

Ahhhh i said i am not great fan of functions so did run without functions

declare @a int=1
Declare @name varchar(50)
while (@a<99999)
Begin 

-- Pass values directly


Select @name= FIRSTNAME + ' ' + MIDDLENAME + ' ' + LASTNAME
        From PERSON


SET @a =@a+1
END

and it ran in 2 seconds in my system

please read the article below

T-SQL User-Defined Functions: the good, the bad, and the ugly

Answer 2

You can see the difference between Estimated Execution Plan and Actual Execution Plan

You are personally expecting select dbo.fn_FormatNameFromId(ID) from PERSON to work as shown on the screenshot: for every row from Query 1 (Clustered Index Scan) do Query 2 (Clustered Index Seek - or Subquery search by ID)

But sql-server sees the overall pattern of these queries and optimizes actual query plan to solely one simple Clustered Index Scan as sql-server does in your second query select dbo.fn_FormatNameFromValues(FIRSTNAME, MIDDLENAME, LASTNAME) FROM PERSON.