Press "Enter" to skip to content

Category: Syntax

Diving into DISTINCT

Published 2026-02-13 by Kevin Feasel

Louis Davidson is one of a kind:

If there is one SQL keyword that causes more fear than any other, it’s DISTINCT. When I see it in a query, I immediately start to worry about just how much work I am in for to ensure the correctness of that query. I start scanning for comments to describe why it is there, and if none are found, I know the query is probably going to be wrong.

I have seen DISTINCT used to hide bad joins, missing grouping, and even missing WHERE clauses. I have seen developers use it as a “fix-all” for data problems.

In this blog, I will look at the proper use and distinctly dangerous uses of DISTINCT and also show how you might test your query that uses DISTINCT to see what it is actually covering up.

Louis also includes one of my “favorite” coding errors: the accidental self-join. Done that one too many times to be proud of.

Leave a Comment

Choosing DENSE_RANK() vs RANK()

Published 2026-02-12 by Kevin Feasel

Jared Westover explains the difference:

Recently, a developer asked for my input on solving a complex SQL query problem. As we went through each step, I suggested using a ranking function at one point, specifically RANK. However, they were having trouble deciding when to use RANK versus DENSE_RANK in a query. It raises a good question: When should you use DENSE_RANK instead of RANK?

Jared’s analogies for when to use which are very similar to the ones I use: a race versus levels.

Leave a Comment

Concatenation via Double Pipe Operator in SQL Server 2025

Published 2026-02-09 by Kevin Feasel

Rajendra Gupta shows off a new operator:

SQL Server 2025 adds the double pipe (||) operator for string concatenation. What is the double pipe (||) operator, and how different is it from the existing plus (+) and CONCAT function for concatenation? Let’s check it out in this article.

I still prefer CONCAT() and CONCAT_WS() for display, and would be indifferent between += and ||= for appending strings. But for companies that need to write ANSI-compliant code, it’s a positive.

Leave a Comment

A Set of T-SQL One-Liners

Published 2026-02-06 by Kevin Feasel

Rebecca Lewis has some quick scripts:

Every DBA has a mental toolbox of go-to queries. Some took years to learn. Some were stumbled upon by chance while working a 2am outage. Today I am sharing 10 of my favorite T-SQL one-liners — the kind of stuff you copy, paste, and immediately feel like a genius. Some are classics, some are new additions — All of them are useful.

Click through for the list. When I was constantly in SQL Server, I’d have a bunch of these types of queries as keyboard shortcuts.

Leave a Comment

Generating a Set of Sequential Numbers Redux

Published 2026-02-06 by Kevin Feasel

Louis Davidson needs even more sequential numbers:

I thought I was done, nice quick little throwaway piece, but I went a little more in depth than I planned. Then Aaron Bertrand messaged me about a post that I had forgotten (even if I did edit it :)), where he was introducing GENERATE_SERIES (GENERATE_SERIES: My new go-to to build sets). In it, he had included a method of doing this using a method that replicates digits and then uses STRING_SPLIT and ROW_NUMBER to generate more digits. He also noted that it was blistering fast.

Aaron (if you know him) is rarely wrong about SQL (at the very least).

I also realized there was one other thing I wanted to add to my tests, that being just selecting from a Numbers/Tally table that has a billion rows. This should be the fastest way to pull a set of numbers.

Read on for one hundred million results. And check out Brent Ozar’s comment on getting things in descending order.

Leave a Comment

Dealing with NULL and Empty String in Multiple RDBMS Platforms

Published 2026-02-05 by Kevin Feasel

Akhil Reddy Banappagari compares three popular platforms:

When you are planning database migrations to PostgreSQL, it is usually the small things that cause the biggest production bugs. One of the most common traps for developers is how different databases handle NULL and empty strings ('').

While they might seem like similar concepts, representing the absence of a value, the way a database engine interprets them can change your query results, break your unique constraints, or cause data loads to fail. In this guide, we will compare the behavior of Oracle, SQL Server, and PostgreSQL to help you avoid common migration pitfalls.

PostgreSQL and SQL Server are close in the way in which they deal with NULL and empty strings, but all three platforms have at least some differentiation, so if you’re deeply familiar with one, the next platform may trip you up a little.

Leave a Comment

Trying the Regex-Based Replace Function in Excel

Published 2026-02-05 by Kevin Feasel

Ben Richardson checks out a new function:

Instead of building up a few different text functions on top of each other, you can now use regex inside Excel formulas to search for patterns, and clean data much more efficiently.

Our favourite of these new additions is REGEXREPLACE, which lets you find text based on patterns and replace data in one simple formula.

Read on to see how the REGEXREPLACE() function works.

Leave a Comment

NOWAIT Hints and Annoyances with Query Store Hints and Plan Guides

Published 2026-02-04 by Kevin Feasel

Erik Darling performs a rather late Airing of Grievances:

In this video, I delve into some of the frustrations and annoyances associated with query store hints and plan guides in SQL Server. I explore how these tools can sometimes hinder rather than help, particularly when trying to override certain behaviors or improve performance. For instance, I demonstrate the quirks of using a `NO_WAIT` hint in a transactional context and highlight why Query Store’s inability to support table hints is such a significant limitation. Additionally, I discuss the cumbersome nature of plan guides, especially their requirement for maintaining semantic affecting hints that might be detrimental to query performance. These issues underscore the need for more robust and flexible tools within SQL Server to better meet the diverse needs of database administrators and developers.

Click through for the video.

Leave a Comment

Thoughts on SELECT *

Published 2026-02-03 by Kevin Feasel

Louis Davidson defends a slighted bit of syntax:

I was about to walk out the door to take a flight, when my phone rang. Our major software system we had just released last week was returning weird data. FirstName and LastName was being reversed. I missed my flight because someone wrote SELECT * instead of SELECT FirstName, LastName and a table structure was reorganized.

Ok, the story was a fabrication, but I wanted to start out with a story that could resonate with the reader. In this post, I want to say a few things about the use of SELECT * and I wanted to make sure it was clear that I am not encouraging more use of SELECT *. Not at all.

I agree with Louis that SELECT * is fine for ad hoc querying. It’s not so great for application code because of the story Louis tells above, but if you’re just checking the contents of a table, whatever. The habit I’d much rather drill into somebody’s head is always have TOP in an ad hoc query. Condition yourself to write SELECT TOP(100) before you start a query. Or have your auto-completion tool of choice (e.g., SQL Prompt) do it for you.

As far as SELECT * or SELECT 1 in EXISTS clauses goes, I used to be in Louis’s camp, though I had a former manager who explained her preference for the latter: if you standardize on SELECT 1 in those clauses, it makes it a lot easier to find inappropriate uses of SELECT * in application code.

1 Comment