Press "Enter" to skip to content

Category: T-SQL

Inline Scalar UDFs in Microsoft Fabric Warehouse

Published 2025-06-24 by Kevin Feasel

Srdjan Martin has an announcement:

SQL native Scalar user-defined functions (UDFs) in Microsoft Fabric Warehouse and SQL analytics endpoint are now in preview.

A scalar UDF is a custom code implemented in T-SQL that accepts parameters, performs an action such as complex calculation, and returns a result of that action as a single value.

I wonder if the same advice for SQL Server will apply to these: don’t use them because they are performance sinks. Knowing that these are scalar functions, I’d be inclined to do some considerable performance testing before rolling them out.

Comments closed

Building a Better Trigger

Published 2025-06-19 by Kevin Feasel

Steve Jones is done with mousetraps:

This seemed to work, but did it really?

The Problem

Let’s illustrate the big problem with this change. I’ll run this code:

Steve hit on one of the problems in trigger design, but there’s another problem that we often see. Steve’s code doesn’t have this problem, but quite often, people assume that rows update one at a time. But if you write a T-SQL UPDATE statement that affects five rows, the trigger fires once for all five. Steve correctly uses the inserted and deleted pseudo-tables to handle all changes, rather than just assuming a single row.

Comments closed

Reshaping Data with the APPLY Operator

Published 2025-06-11 by Kevin Feasel

I have a new video:

In this video, I show how we can use the APPLY operator to reshape datasets, allowing us to unpivot tables and also calculate the greatest and least values for a row.

If you look closely at the scripts, you’ll see 08 and 10. In the source control repo, I also have a script 09 that covers splitting strings. Using APPLY to split strings has always been a bit of a niche case, but prior to SQL Server 2016’s introduction of STRING_SPLIT() and SQL Server 2022’s improvement of the function, I could make the case that it sometimes made sense to know how to split strings via APPLY. Today, not so much, which is why I tossed that demo from the video.

Comments closed

Simplifying Calculations with the APPLY Operator

Published 2025-06-04 by Kevin Feasel

I have a new video:

In this video, I show how we can use the APPLY operator to remove redundancy in the SELECT clause and simplify complex calculations, all with zero performance impact.

This is, as I’ve said in the past, my favorite use case for the APPLY operator. As I’ve become older and (even) more crochety, I’ve sided more and more with “easy to read” versus “runs faster” for code. And when you get “easy to read” with no impact on “runs faster,” I’m all in.

The accounting scenario I show may be a fairly extreme case, but I’d bet that queries similar to this abound in almost any company. A big part of why complex reporting queries are so complex comes from repetition of calculations.

Comments closed

Retrieving Child Records with APPLY

Published 2025-05-28 by Kevin Feasel

I have a new video:

In this video, I show how we can use the APPLY operator to find records associated with a “parent” table. We look at getting the latest record, as well as any arbitrary number of records. We also spend time digging into under what circumstances the APPLY operator out-performs other common options.

During the outro segment, I dig into under what circumstances APPLY is the best tool for the job, so stick around for the end.

1 Comment

Combining DISTINCT and UNION

Published 2025-05-27 by Kevin Feasel

Louis Davidson gives it the college try:

When I was perusing my LinkedIn feed the other day, I came across this thread about using SELECT *. In one of the replies, Aaron Cutshall noted that: “Another real performance killer is SELECT DISTINCT especially when combined with UNION. I have a whole list of commonly used hidden performance killers!”

To which started my brain thinking… What does happen when you use these together? And when you use UNION on a set with non-distinct rows, what happens. So for the next few hours I started writing.

Read on for Louis’s findings.

Comments closed

T-SQL Snapshot Backups to FlashArray

Published 2025-05-21 by Kevin Feasel

Anthony Nocentino cuts out the middleman:

In this post, I’ll walk you through a T-SQL script that creates application-consistent snapshots on Pure Storage FlashArray, all from within SQL Server, no external tooling. SQL Server 2025 introduces a powerful new feature: the sp_invoke_external_rest_endpoint stored procedure. This enhancement makes calling REST APIs directly from T-SQL easier than ever. Combining this new capability with Pure Storage’s API allows us to orchestrate snapshot operations seamlessly, with no external tools or scripts required.

Click through for the process. I know that sp_invoke_external_rest_endpoint will be controversial for DBAs. That’s why I think it’s good to have examples of how it can be useful before the knee-jerk reaction of “this is automatically bad” takes over.

Comments closed