T-SQL User-Defined Functions, or: How to kill performance in one, easy step

This page contains the description for my conference session “T-SQL User-Defined Functions, or: How to kill performance in one, easy step”, links to the slide deck and demo code used in this presentation, and links to recordings of past deliveries.

Description

Target audience Database developers and DBAs with enough experience to write and understand basic T-SQL queries.
Short description An overview of the various types of user-defined functions available in SQL Server and their performance impact.
Duration The ideal length for this session is 60 – 75 minutes.
Full abstract User-defined functions in SQL Server are very much like custom methods and properties in .Net languages. At first sight, they seem to be the perfect tool to introduce code encapsulation and reuse in T-SQL. So why is this feature mostly avoided by all T-SQL gurus?

The reason is performance. In this session, you will learn how user-defined functions feed the optimizer with misleading and insufficient information, how the optimizer fails to use even what little information it has, and how this can lead to shocking query performance.

However, you will also see that there is a way to avoid the problems. With just a little extra effort, you can reap the benefits of code encapsulation and reuse, and still get good performance.

Resources

Click here to download a ZIP file containing the slide deck and demo code used in this presentation.

(Note: my presentations tend to evolve over time, so the version you find here may not be an exact match for the version you witnessed)

Recordings

Not able to attend a conference and still interested in this session? This session was recorded by GroupBy when I presented it for them, and you can watch their recording here.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close