A Fine Slice Of SQL Server
Erik Darling talks about one of my favorite T-SQL features.
As usual, Erik leaves me hanging with respect to the lack of description or snippet I can use as a graf for enticing my wonderful audience to watch his video. Thus, I have to come up with my own. Erik’s video is actually a really good companion piece to my video that also dropped this week, as we both cover the same general concept.
Leave a CommentIn 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 CommentLouis 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 DISTINCTespecially when combined withUNION. 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.
Leave a CommentAnthony 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_endpointstored 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.
In this video, I show how we can use the APPLY operator to operate on ad hoc functions. That leads to a powerful use case: pre-aggregating data.
Every once in a while, this tip will save a considerable amount of CPU time and database effort.
Leave a CommentJeffry Schwartz looks for exceptions:
Given the complexity, I realized that validating all intermediate and final result sets was essential to ensure that tuning changes did not alter any report results. To support this validation, I saved interim and final result sets into tables for direct comparison.
For these comparisons, the EXCEPT and INTERSECT operators proved invaluable.
Click through for the full story. I’ve always liked using these set operations in ETL jobs because they automatically know how to handle NULL, so this approach is more robust than rigging your own comparisons.
Dennes Torres tries out the JSON data type in Azure SQL Database:
Before this new field type, JSON data was typically stored in
varchar(max)columns. There are many features to use with JSON values stored invarchar(max)columns and variables, but storing JSON as regular strings is still limited.The built-in JSON type expands the possibilities. Using an actual JSON column, it becomes easier to build constraints related to JSON columns, for example.
Dennes also spends a lot of the article covering the JSON_ARRAYAGG() and JSON_OBJECTAGG() functions.
In this video, I explain the history behind the APPLY operator in T-SQL. I also demonstrate one use case for it: executing table-valued functions.
As I mention at the end of the video, this is by far the least interesting use case for the APPLY operator. Subsequent videos in the series get a lot more exciting. But we have to start at the beginning, unless we’re going full Pulp Fiction.
Comments closedVlad Drumea counts that which does not exist:
I’ve seen variations of the question “how to count all NULLs in all columns of a table” pop up on reddit once every couple of months, and I figured I’d give it a shot and post here in case anyone else might need it in the future.
Click through for the script. It does require M*N scans, where M represents the average number of columns in each table and N the number of tables in the database. In other words, don’t expect an immediate response.
Comments closedAndy Brownsword puts on the lab coat and performs some experiments:
The
UNIONandUNION ALLoperators allow us to combine results, but there’s no guarantee that each set of results uses the same data types. So what data types are returned?For the longest time I thought the data types from the first set of results were used for the final results. That’s not the case.
Read on to see what the rules look like.
Comments closed