Press "Enter" to skip to content

Category: Syntax

DISTINCT vs VALUES in DAX

Published 2025-07-02 by Kevin Feasel

Marco Russo and Alberto Ferrari compare two keywords:

When you begin modelling in DAX, DISTINCT and VALUES often appear interchangeable: both return the list of unique values for a column in the current filter context. In a clean development model, they behave the same, so it is easy to pick one at random – or worse, swap between them without thinking.

However, they are not identical. The subtle difference is crucial in production models that may one day contain invalid relationships or bad data. 

Read on to see how each works and how they differ in practice.

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

psql Meta-Commands

Published 2025-06-02 by Kevin Feasel

Ian Parker shows off some meta-commands:

If you manage PostgreSQL from a terminal you already know psql, the interactive client that ships with every installation. Most developers use it for the basics—running SELECT statements, loading a .sql file, maybe poking around with \dt to see which tables exist.

Beneath that familiar surface, though, psql hides a rich toolbox of meta-commands. These commands, all prefixed with a backslash, live inside the client. They’re not SQL, they’re shortcuts built into psql itself, and they can make everyday tasks faster and far less error-prone.

Read on for six of these, including examples like \watch to view something with periodic refresh.

Comments closed

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

Set-Based Comparisons for Data Validation

Published 2025-05-12 by Kevin Feasel

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.

Comments closed

Working with the JSON Data Type in Azure SQL DB

Published 2025-05-08 by Kevin Feasel

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.

Comments closed