O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 25. Building User-Defined Functions

IN THIS CHAPTER

  • Creating scalar functions

  • Replacing views with inline table-valued functions

  • Using complex code within multi-statement table-valued functions to generate a result set

SQL Server 2000 introduced user-defined functions (UDFs), and the SQL Server community was initially slow to adopt them. Nevertheless, UDFs were my personal favorite new feature in SQL Server 2000, and I still use them frequently.

The community discovered that UDFs can be used to embed complex T-SQL logic within a query, and problems that were impossible or required cursors could now be solved with UDFs. The result is that UDFs have become a favorite tool in the toolbox of any serious SQL Server database developer.

The benefits of UDFs can be easily listed:

  • UDFs can be used to embed complex logic within a query. This is huge. I've solved several nasty problems using user-defined functions.

  • UDFs can be used to create new functions for complex expressions.

  • UDFs offer the benefits of views because they can be used within the FROM clause of a SELECT statement or an expression, and they can be schema-bound. In addition, user-defined functions can accept parameters, whereas views cannot.

  • UDFs offer the benefits of stored procedures because they are compiled and optimized in the same way.

The chief argument against developing with user-defined functions has to do with potential performance issues if they're misused. Any function, user-defined or system that must be executed ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required