Press "Enter" to skip to content

Category: Syntax

RIGHT OUTER JOIN and MERGE

Published 2025-03-14 by Kevin Feasel

Lukas Eder merges lanes:

It’s not unreasonable to expect these two statements to produce the same execution plan on most RDBMS, given that they’re logically equivalent. Since we’ve grown used to reading things from left to right and top to bottom, I don’t think RIGHT JOIN will become more popular any time soon.

There is, however, one place in the SQL language where RIGHT JOIN is surprisingly ubiquitous!

Read on to see how the MERGE operator includes as part of its operations the equivalent of a RIGHT OUTER JOIN. MERGE in SQL Server had justifiably built a bad reputation in its early years, but it’s surprisingly okay nowadays, with most of the bugs being fixed over time.

Comments closed

Performance Comparison: Tally Table vs GENERATE_SERIES()

Published 2025-03-06 by Kevin Feasel

Steve Jones performs a pair of tests:

I had someone reach out about generate_series() recently, saying they hadn’t realized this was a new feature in SQL Server 2022. They were wondering if it was better than using a tally table.

I didn’t want to do an exhaustive test, but I thought I’d take a minute and try a couple simple things just to see.

Steve used the CTE-based tally table builder, building based on cross joining spt_values. This is one of the classic approaches. The performance differences aren’t enough on their own to justify large-scale changes if you’re using a classical tally table, though it is good to see that GENERATE_SERIES() does perform well. And if you’re not familiar with the power of a tally table, here is one great explanation of the concept.

Comments closed

Random Functions in PostgreSQL 17

Published 2025-03-06 by Kevin Feasel

Leo Hsu and Regina Obe look at updates to the random() function in PostgreSQL:

Have you ever wanted to get a random integer between 1 and 10 and been a little annoyed the slightly cryptic code you had to write in PostgreSQL? PostgreSQL 17 random functions make that simpler. Sometimes it’s the small changes that bring the most joy.

Click through to see what it took to get a random integer or floating point number prior to PostgreSQL 17 and how it’s a fair bit simpler today.

Comments closed

Removing Unnecessary Delete Operations

Published 2025-02-28 by Kevin Feasel

Monica Rathbun truncates some data:

SQL Server performance issues often stem from easy fix bottle necks that can be fixed with the right tuning strategies. This short blog will focus on the DELETE statement.

Click through for the full story. One minor correction that I’d offer is that the TRUNCATE TABLE command is logged, which is why you can roll it back in a transaction. The logging process is much less intensive than with DELETE because of the deferred drop logic that Paul talks about in the link.

That aside, Monica’s key point is absolutely correct: DELETE operations tend to be quite slow, especially as the number of records you need to delete increases. This is where techniques like batching delete operations can help reduce the pain level, but if you’re deleting every (or almost every) row from a table, there’s probably a better method. Unless replication is involved in the mix—in that case, there are no good methods for anything because replication hates us.

Comments closed

Sales KPIs in T-SQL

Published 2025-02-26 by Kevin Feasel

I have a new video:

In this video, we will take a look at several useful KPIs for measuring sales. We will also demonstrate the utility of the APPLY operator, the WINDOW clause, and the APPROX_PERCENTILE_CONT() function.

I enjoyed putting this one together, because I enjoy it any time I have a chance to talk about the APPLY operator.

Comments closed

Building a RegEx Emulator in SQL Server

Published 2025-02-26 by Kevin Feasel

Sebastiao Pereira offers a fourth-best solution:

Regular expressions (REGEX) let you adaptively investigate, employ, and authenticate text data. This makes it easy to search for simple or complex string patters. There is no direct way to do this in SQL Server, but in this article we look at some SQL functions you can create to emulate regex like functionality.

Regular expressions are coming to SQL Server 2025 and are in Azure SQL Database, so that’s the best option when it becomes available. The second-best option is to use CLR and offload your regular expressions work to .NET, especially if you’re using a library like SQLSharp to do so. The third-best option would be to do this in Python or R with ML Services, though that’s going to be a bit of setup effort and will probably be somewhat limiting. And if all else fails, this is an admirable fallback.

Comments closed

Working with JSON_OBJECTAGG() and JSON_ARRAYAGG() in Azure SQL

Published 2025-02-25 by Kevin Feasel

Koen Verbeek tries out a couple of fairly new functions:

I need to construct JSON from data in our database, but I find the existing FOR JSON PATH limited when the data is not located in one single row but rather scattered over multiple rows. Is there another method on how to handle JSON data in SQL Server? Learn how to use the new SQL Server JSON Functions JSON_OBJECTAGG and JSON_ARRAYAGG in this article.

These aren’t available on-premises yet, though given that there’s a new version of SQL Server coming out in 2025, there’s a good chance we’ll have it by then.

Comments closed

Discerning the Value of an Empty Table

Published 2025-02-20 by Kevin Feasel

Andy Levy performs some database archaeology:

It seems like no matter how long you work with a system beyond a trivial size, you’ll find something new every so often. A little while ago, I found a table without about a dozen columns, no data, and was referenced in only one place across an entire database hosting nearly 1000 tables and over 8000 stored procedures. Why does this thing even exist?

To protect the innocent(ish?), obviously I’m changing the names of everything here. I was looking into some performance issues and found a very short stored procedure being called from another stored procedure which opened with this query:

Click through for the query and why it existed in the first place.

Comments closed

Experimenting with BIT_COUNT

Published 2025-02-10 by Kevin Feasel

Louis Davidson has an idea:

I was editing an article the other day that uses the BIT_COUNT function that was added to SQL Server 2022. The solution presented is excellent, and I will try to come back and link to it here when I remember. (It will be linked the other way.

Reading that did two things for me. First it cave me an idea of how the BIT_COUNT function might be actually be used in a useful way. The solution that was presented would only work in SQL Server 2022 (It will work in earlier compatibility levels, based on the tests I have done.)

Read on for what Louis tried out.

Comments closed