Database Function VS Case Statement

Question

Yesterday we got a scenario where had to get type of a db field and on base of that we had to write the description of the field. Like

Select ( Case DB_Type When 'I' Then 'Intermediate'
                      When 'P' Then 'Pending'
                      Else 'Basic'
         End)
From DB_table

I suggested to write a db function instead of this case statement because that would be more reusable. Like

Select dbo.GetTypeName(DB_Type) 
from DB_table

The interesting part is, One of our developer said using database function will be inefficient as database functions are slower than Case statement. I searched over the internet to find the answer which is better approach in terms of efficiency but unfortunately I found nothing that could be considered satisfied answer. Please enlighten me with your thoughts, which approach is better?

Answer 1

UDF function is always slower than case statements

Please refer the article

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/10/06/performance-benefits-of-using-expression-over-user-defined-functions.aspx

The following article suggests you when to use UDF

http://www.sql-server-performance.com/2005/sql-server-udfs/

Summary :

There is a large performance penalty paid when User defined functions is used.This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. If the UDF is used in the WHERE clause, this may happen as part of the filtering the rows. If the UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It's the row by row processing that slows SQL Server the most.