Search results for “scalar functions”

  1. Home
  2. Search Results

Compute Scalar Functions

Functions, functions, functions sp_BlitzCache warns about two things: UDFs CLR UDFs For Regular UDFs There can be serious performance consequences when using Scalar and Multi-Statement Table Valued Functions. Inhibit parallelism Run once per row returned Cause poor cardinality estimates You may even see these warnings alongside a warning for Forced Serialization. Read more here: Are…
Read More

03a – How to Write Functions That Scale

Here’s what you’re going to learn in this demo: Why scalar-valued functions don’t scale How to reduce their overhead with a couple of options Why multi-statement table-valued functions don’t scale How to dramatically cut their overhead by inlining them Then for a bonus: Which kinds of functions go faster in SQL Server 2019 Which kinds…

We don’t sell this anymore, but check out our other training options.
Read More

Finding Froid’s Limits: Testing Inlined User-Defined Functions

This week, I’ve been writing about how SQL Server 2019’s bringing a few new features to mitigate parameter sniffing, but they’re more complex than they appear at first glance: adaptive memory grants, air_quote_actual plans, and adaptive joins. Today, let’s talk about another common cause of wildly varying durations for a single query: user-defined functions. Scalar…
Read More

2.2 User-Defined Functions

Scalar UDFs, multi-statement table-valued functions, and inline functions: only the last one performs well. Brent demos all 3, shows you how they hide in execution plans, and explains how SQL Server 2019 helps. Demo Script Transact-SQL /* Mastering Query Tuning – Why 2 Out of 3 Functions Don’t Scale This script is from our Mastering…

To access this incredible, amazing content, you gotta get Live Class Season Pass, Live Class Season Pass PLUS Lab VM or Mastering Query Tuning, or log in if you already shelled out the cash.
Read More

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

But is it faster? Now, I know. There are very few “always” things out there in SQL Server. This is also true for functions. A lot of the time — I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.…
Read More

3. Functions (39m)

You’ve got code. You want it to function, so you write it as a function. Then your queries get slow, and you can’t figure out why. tl;dr — it’s your functions. Let’s talk about why, and how you can fix them. Transcript: Hello ladies and gentlemen, and welcome back to your Brent Ozar Unlimited video…

To access this incredible, amazing content, you gotta get Fundamentals of Query Tuning or Recorded Class Season Pass, or log in if you already shelled out the cash.
Read More

2.5 Functions (17m)

Developers are taught to reuse code by following design patterns like DRY (don’t repeat yourself). That means we usually get started writing our T-SQL in functions. Unfortunately, it turns out that scalar UDFs and multi-statement table-valued UDFs perform horribly. You’ll learn that they don’t even show up in execution plans, and how to replace them with inline…

We don’t sell this anymore, but check out our other training options.
Read More

Inline Table Valued Functions: Parameter Snorting

You’ve probably heard about parameter sniffing But there’s an even more insidious menace out there: Parameter Snorting. It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at…
Read More