Join Joey D'Antoni for an in-depth discussion in this video Why scalar UDFs are expensive, part of SQL Server Performance for Developers.
- [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…into that call.…They're also interpreted for every call,…
- Query execution
- How to read an execution plan
- What not to do with SQL Server
- Why cursors are bad
- Why scalar UDFs are expensive
- Datatypes and design
- What is a columnstore index?
- Transaction isolation
Skill Level Intermediate
1. Query Execution
2. What Not to Do with SQL Server
3. Datatypes and Design
4. Temporary Objects
7. Isolation Levels
Next steps2m 15s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.