VS Case Statement Database Function

Question

Yesterday we had a script where we needed to get the db field type, and based on this we had to write a description of the field. how

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

I suggested writing a db function instead of this case argument, because that would be more reusable. how

 Select dbo.GetTypeName(DB_Type) from DB_table 

The interesting part: one of our developers said that using a database function would be inefficient , since database functions slower than Case statement . I searched the Internet to find an answer that is better suited in terms of efficiency, but unfortunately I did not find anything that could be considered a satisfied answer. Please enlighten me with your thoughts, which approach is better?

Answer 1

 UDF function is always slower than case statements 

Please refer to 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 shows how to use UDF.

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

Summary:

When using user-defined functions, there is a large execution penalty. This penalty is manifested in the form of a low query execution time when the query applies UDF to a large number of rows, usually 1000 or more. The penalty is incurred because the SQL Server database engine must create its own internal cursor, such as processing. It should call every UDF for every row. If UDF is used in a WHERE clause, this can happen as part of string filtering. If UDF is used in the selection list, this happens when you create the query results to go to the next stage of query processing. This is line by line processing, which slows down SQL Server the most.

Answer 2

Your demonstrator is right. Features will slow down your request.

http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-1.aspx

 Calling functionsis like: wrap parts into paper put it into a bag carry it to the mechanics let him unwrap, do something, wrapt then result carry it back use it 

Answer 3

When using a scalar function (a function that returns a single value), the contents of the function will be executed once per line, but the case statement will be executed throughout the set.

When working with the whole set, you allow the server to optimize your request more efficiently.

So, the theory says that the same query works in both directions against a large data set, then the function should be slower. However, the difference may be trivial when working with your data, so you should try both methods and test them to determine whether performance compromise is worth the benefit of the function.