Category: Syntax

Louis Davidson gets back to basics:

For a while now, I really have wanted to learn the Windowing Functions in great detail. I know them well enough to make it through a lot of needs, but there are a lot of intricacies that are hard to remember. As I noted in my very first Blogging for Programmers post, writing for your own future needs is some of the best and easiest writing you will do.

When I wrote my “SQL Techniques you should know” presentation, the topic that took the longest was Window Functions. Because of their similarity to GROUP BY, I figured this was the best place to start.

Read on as Louis works through the concept.

Leave a Comment

Dualcore DBA demonstrates how you can turn an outer join into an inner join on accident:

This post is a quick public service announcement to serve as a reminder to be careful with joins in SQL, particularly in queries with a large number of INNER and LEFT JOINs.

An INNER JOIN onto a LEFT JOIN will effectively make that LEFT JOIN an INNER JOIN.

This is a bit different from the other major case: a LEFT OUTER JOIN whose column you use in the WHERE or HAVING clauses.

Leave a Comment

Jerry Nixon shares some options:

Application developers already know what happens when one method does everything: it becomes difficult to read, test, reason over, and safely change. We use patterns like decomposition, encapsulation, and explicit dependencies because they solve those problems.

T-SQL does not give us classes, inheritance, interfaces, or polymorphism in the same way C# does, but that does not mean good software practices stop applying when logic moves into the database.

Decomposition is a good example. Breaking complex database logic into sensible, well-defined components can reduce complexity, improve readability and maintainability, and make individual pieces easier to test. These are established, respected, and proven techniques for building great software, whether the code runs in an application or inside the database.

My problem is, there are performance costs to T-SQL decomposition. Unfortunately, Jerry doesn’t cover that at all in his post, but attempts at decomposing in SQL Server often fail for exactly that reason.

Comments closed

Steve Jones tries out a function:

I was trolling the docs and noticed the SIGN() function. I have never written this in production code, but it is an interesting function. This post looks at where I might use this and when the need arises.

I knew of the SIGN() function but have never used it before I think a simple CASE expression with an inequality predicate is more my speed.

Comments closed

John Deardurff has some advice:

I’ve been reviewing Azure SQL Database performance guidance recently and came across Microsoft’s documentation on How to Use Batching to Improve Application Performance. While the article focuses on Azure SQL Database, the same principles of batching transactions for better performance apply equally well to SQL Server and Azure SQL Managed Instance.

The reason this topic caught my attention is that batching doesn’t just improve performance. It can reduce blocking, minimize rollback pain, improve transaction log efficiency, and potentially lower costs in cloud environments. That’s a pretty good return on investment for a relatively simple coding change. Here is the SQL Script that I use for this demonstration. Feel free to test for yourself. (It is a text file, so you will have to save it as a .sql file.)

Batching is especially important on delete operations against larger tables, where you don’t remove enough data to make TRUNCATE TABLE a viable alternative (or where you don’t have permissions to truncate). But one thing to keep in mind is that index design matters for batch operations. If you don’t have a good index, your first batches will be fast but they will gradually slow down as SQL Server needs to scan an increasingly large range to find the next set of rows to update. I wrote about this quite a while ago when putting together a talk on near-zero downtime T-SQL operations.

1 Comment

Greg Low makes use of an uncommon function:

If your T-SQL is buried under layers of nested REPLACE() calls just to swap out a few characters, there’s a simpler way. SQL Server’s TRANSLATE() function — introduced in 2017 but still rarely used — lets you replace multiple characters in a single string with one function call instead of stacking several REPLACE() functions inside each other.

This guide covers how TRANSLATE() works, how it compares to REPLACE(), and where it falls short compared to other SQL dialects like PostgreSQL and Oracle.

One important thing to remember with TRANSLATE() is that it’s a character-for-character replacement. In other words, if you TRANSLATE('ABCDEFG', 'x'), it will replace every instance of each of those characters with the letter “x.” It doesn’t replace a substring.

Comments closed