Using Inner join or Function(Case)

One of the developer using function in query to return the result that he needed. The query and write using inner join without function
What is the different ?

Example :
In adventurework :

create table HumanResources.Gender
(id char(1),
Name varchar(20))

insert into HumanResources.Gender
values ('M','This is Male')
insert into HumanResources.Gender
values ('F','This is Female')

create FUNCTION dbo.GetGender (@what char(1))
RETURNS varchar(20)
AS
BEGIN
Declare @whatGender varchar(20)
SELECT @whatGender=Name from HumanResources.Gender where id=@what
RETURN @whatGender
End

Run Display Estimated Execution plan
together for the above 2 query



What Estimated Execution plan above show?
First query Query cost is 69% compare to second query Cost 31% (25%+6%)
Is that mean second query better that first query ?


Let set statistics time on, click on
and actual run the query
set statistics time on
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
set statistics time off

Actual execution plan


From the actual exection plan
First query still have high relative cost compare to second query

From the statistics
First query CPU time = 0 ms, elapsed time = 83 ms.
compare to Second query CPU time = 15 ms, elapsed time = 91 ms.
Second query elapsed time more slower compare to first query and use more CPU resource.


Let turn on Profiler to see what is running when we execute the query

Open SQL Profiler and select only TSQL:SQL:Batch Complete and click run
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 15, Read 19, Write 0, Duration 85

In Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 31, Read 893, Write 0, Duration 85

It show First query use less resource and run more faster compare to Second query.

Go back to SQL Server Profiler, pause the trace
From File->Properties->Event Selection->Check on Show all events-> Go to TSQL Category and check on SP:StmtCompleted (SQL statement within a stored procedure has completed).
Press run to run SQL profiler again.

From Menu bar, click on "Clear Trace Window" to clear the early trace

In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

Go back to SQL Profiler and check the result
That is only 1 line in the SQL Profiler, SQL:BatchCompleted with CPU 31, Read 893, Write 0, Duration 85


Let clear Trace Window in SQL Proiler to clear to trace result and go back to Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a

Go back to SQL Profiler and check the result
SP:stmtCompleted with
- SELECT @whatGender=Name from HumanResources.Gender where id=@what
- RETURN @whatGender

Last result is SQL:BatchCompleted with query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a



When we execute Second query, SQL need to get Name value from HumanResources.Gender and return the value to GetGender function.

If we have 291 result return, SQL will execute (291*2)+1 time in SQL Server compare to first query which only execute one

From the result above, we can conclude first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

is better than second query

SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a