Category: T-SQL

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed