Category: T-SQL

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.

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.

Andy Brownsword gets beyond SQL Server 2000:

Date handling in SQL Server tends to accumulate tried and trusted combinations of DATEPART(), DATEADD(), and DATEDIFF() – with nested variations. The challenge with these isn’t raw performance, but more with conveying intent and readability.

So let’s look at some simpler patterns to try and avoid some of these and be clearer with what we’re trying to achieve.

Click through for some functions that have been around since 2012, and others that just became available in 2022.

Steve Jones answers a question:

Someone asked why I would use TRY_PARSE after I posted a question at SQL Server Central: Getting the Average. Isn’t is slower?

A fair question. This quick post looks at why.

I’d use TRY_CAST() or TRY_CONVERT() in this particular scenario. The main reason I’d use TRY_PARSE() would be if you need .NET-specific parsing functionality, such as parsing dates by a specific locale. The reason is that PARSE() and TRY_PARSE() are an order of magnitude slower than their CAST() and CONVERT() cousins.

That said, Steve’s example reminds me of a PolyBase demo I used to do, in which I took a CSV of North Carolina populations by county and read in the information. In that particular dataset, they would use the letter “A” to describe either “Not enough people to show an answer without potentially violating PII” or “We don’t know what the answer is.” So even though it was clearly a numeric attribute in “Population,” the output process overloaded the definition of that attribute and the only way you would know is to happen to see the three rows in ~1500 that happened to have an A in the column value.

Andy Brownsword moves an expression:

Computed columns allow us to bake logic into our schema. I find particular use for these when reviewing or optimising established solutions where you spot the quirky ways the data is being used.

Here I want to demonstrate 3 symptoms and show how different implementations of computed columns solve them.

Click through for the technique. It certainly won’t solve every performance problem relating to non-SARGable functions, but it does work pretty well on specific classes where you have limited control over the code.