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 - SQL Server Tutorial
From the course: SQL Server Performance for Developers
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…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.