Press "Enter" to skip to content

Category: Syntax

Combining DISTINCT and UNION

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.

Leave a Comment

T-SQL Snapshot Backups to FlashArray

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.

Leave a Comment

Set-Based Comparisons for Data Validation

Jeffry 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.

Leave a Comment

Working with the JSON Data Type in Azure SQL DB

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 in varchar(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.

Leave a Comment

The APPLY Operator in T-SQL

I have a new video:

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 closed

Queue-Style Batch Deletion Woes in PostgreSQL

Shayon Mukherjee runs into an interesting issue:

I recently discovered an unexpected behavior in PostgreSQL involving a pattern of using a Common Table Expression (CTE) with DELETE ... RETURNING and LIMIT to process a batch of items from a queue-like table. What seemed straightforward turned out to have a surprising interaction with the query planner.

Click through to see what happened, as well as Shayon’s recommendation on how to fix it.

Comments closed

Performing a Quick Filter via APPLY

Shane O’Neill tries something out:

Now, SQL Server doesn’t have the filter option, but we can do some pretty weird things, like a SELECT...WHERE statement with no FROM clause.

SELECT

    a,b,c,

    [filter?] = (SELECTb WHEREb > 11)

FROMz;

GO

It turns out that this works, but when you try to aggregate the results, it doesn’t work the way Shane expected. Using the APPLY operator does help here, so click through to see how that works.

Comments closed

Session-Scoped Temp Tables in Microsoft Fabric now GA

Twinkle Cyril gets something GA:

Introducing distributed session-scoped temporary (#temp) tables in Fabric Data Warehouse and Fabric Lakehouse SQL Endpoints.

#temp tables have been a feature of Microsoft SQL Server (and other database systems) for many years. In the current implementation of Fabric data warehouse, #temp tables are session scoped or local temp tables. Global temp tables are not included in this release.

Session-scoped #temp tables exist only within the session in which they are created and last only for the duration of that session. They are not visible to other users or sessions and are automatically dropped from the system once the session ends or the user decides to drop the temp table. These tables are accessible to all users without requiring specific artifact-level permission.

Click through for examples of how it works and how you can specify a session-level temp table over a local temp table.

Comments closed