Category: Syntax

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

Erik Darling defends the honor of the MERGE statement:

In this video, I delve into the often maligned `MERGE` statement in SQL Server, defending it against its critics and highlighting its unique benefits. You might be surprised to learn that `MERGE` can actually help catch non-deterministic updates—something a standard `UPDATE` query won’t do. By demonstrating how `MERGE` issues a warning when you attempt a non-deterministic update, I aim to show why this statement deserves more respect in your database toolkit. The video also covers practical solutions for making these updates deterministic using window functions like `ROW_NUMBER()`, ensuring that the data integrity and consistency you expect from SQL Server are maintained. Whether you’re looking to improve query reliability or just want to understand a lesser-used feature better, there’s something here for everyone who deals with complex data operations in SQL Server.

Alongside this, I recommend a post from Hugo Kornelis from a few years ago, looking into pretty much every problem people reported with MERGE and checking to see if it was still and issue and, if so, under what circumstances.

Leave a Comment

Louis Davidson breaks out the stopwatch:

I have read so much lately about how bad it is to use a recursive CTE to do… well pretty much anything. It came up in a discussion about creating sequential number, and not in a positive sort of way.

I wrote about recursive CTEs when they were first added to SQL Server, and have been a fan for doing breadth-first searching of a hierarchy/graph, but never even thought to use one to generate a set of numbers. As I kept hearing how bad it this method was, so I figured, let’s see just how terrible it is. And of course, I needed something to compare to, so I decided to try all of the ways I could think of. So I will generate varying numbers of rows with the following methods:

Read on for the list of competitors and how they did.

Leave a Comment

Koen Verbeeck makes use of regular expressions:

I have text stored in my SQL Server database, and I want to split it into all its different words. T-SQL has supported the STRING_SPLIT function since SQL Server 2016, but it only allows one single delimiter. There are multiple possible delimiters, such as commas, spaces, carriage returns and so on. It seems quite cumbersome to nest multiple STRING_SPLIT using APPLY. Is there a better option out there that doesn’t involve custom coding with CLR?

Read on to learn more. My understanding is that this method is similar in terms of performance to the other mechanisms we have available, like STRING_SPLIT(), tally tables, the APPLY operator, and CLR functions.

Leave a Comment

Louis Davidson tries out a function:

Using this function, you can group data easily into different time buckets, like year, month, day (which are standard enough, of course), but also into buckets like 2 days, 6.4 weeks, etc. Now, I don’t feel like this should ever make you decide that you don’t need a date dimension for you warehouse, but it is great when you are are just checking out data and want to play with different intervals in an easy manner.

Read on to see how it works.

Leave a Comment

Jared Westover dives in:

Microsoft added several new features in SQL Server 2025, including an exciting suite of regular expression (regex) functions. After years of anticipation, there’s no longer a need to rely on CLR to use regex capabilities. As an experienced SQL developer, I enjoy finding specific rows, and the function that stands out to me is REGEXP_LIKE. I was drawn to it by its name, mainly because I frequently use the LIKE predicate. Right now, we are deciding whether to use it.

Read on for the results of Jared’s testing.

Comments closed

Koen Verbeeck wants to catch them all:

We have a use case where we need to extract a substring of a text. In addition, it needs to match a regular expression (regex). It’s also possible there are multiple matches in one piece of text. How can we efficiently extract those substrings without using a loop or a cursor?

Read on to see how this works.

Comments closed