From the course: SQL Server Performance for Developers

Unlock the full course today

Join today to access over 22,400 courses taught by industry experts or purchase this course individually.

Why scalar UDFs are expensive

Why scalar UDFs are expensive - SQL Server Tutorial

From the course: SQL Server Performance for Developers

Start my 1-month free trial

Why scalar UDFs are expensive

- [Instructor] Scalar user defined functions have a really bad reputation in SQL server, and especially in terms of performance. While they can make ease of code reuse very beneficial, for example if you want to get a count of a certain column and you want to be able to in line that in your queries can be a very useful mechanism for doing that. There are some severe negative ramifications in terms of performance of using a user defined function in line. For small volumes of data it may not be a big deal but they have a number of poor performing attributes that we'll see. So the biggest one is that we lose the ability to do parallelism, so we'll see this in our demo. If you have a plan that takes advantage of parallelism, and you'll see this a lot especially when you're dealing with larger volumes of data on bigger servers, in that you'll get a lot of benefit out of having a parallel query plan. Your plan will simply go single-threaded as soon as you introduce a user defined function…

Contents