Voice of the DBA

Tag: T-SQL

Refactoring SQL Code

One of the things I see software developers often talking about is how they refactor code. As they touch a class, method, etc., they may take the time to refactor the code to make it cleaner, perform better, or just add some documentation. It seems that a regular part of a software developer’s job is refactoring code in the codebase.

That is unless they see a “don’t touch this, no idea how it works” comment. There are plenty of those, and often everyone leaves that code alone.

I was thinking about this when I saw this article on strategies to refactor sql code. The article seems written more for PostgreSQL, but there are items that relate to T-SQL as well. The main thrust of the article is about trying to rewrite code to DRY (don’t repeat yourself). The more changes you can make to shrink code, either to make it easier to read or avoid repeating those copy/paste items, the better off your team will be. It’s easy to think those copies aren’t a big deal, but it’s easy to update code in one place because that solves the problem you were given, and forget to fix all the copies.

I don’t know that anyone should implement all the techniques listed, but they are things to think about. Using CTEs, Views, APPLY, the WINDOW clause, and more can help improve the health of your codebase and make it easier for all the members of your team to understand how the system works.

I wonder how many of you have a refactor mentality when you touch code, or do you tend to leave things alone and add new queries/objects/etc. to your database. I wonder if the fear of breaking something that might be used by other code is on your mind. Or maybe you suffer from “not invented here” (NIH) and just add your own code.

If you refactor code, then what things do you look to change or improve? Any tips/tricks/guidelines you’d share with others? If you don’t refactor code, why not?

I think testing is a big part of refactoring. If you have tests, then you can be less worried about your changes breaking something. There is a great video on practical refactoring. It’s from the software engineering view, and it’s long, but it’s worth a watch if you have a few moments.

I wish more people tested their SQL code and refactored poorly written (or poorly performing) code on a regular basis.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

December 12, 2025
Database Collation Matters for Unicode: #SQLNewBlogger
While trying to work with Unicode data, I found some issues with collation. This post showcases what I’ve seen, with probably not enough answers. The collation/UTF stuff is still slightly confusing to me.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Noticing Problems

I was doing some testing with Unicode data and noticed this sentence in the docs for UNISTR() (image below): “The database collation must be a UTF-8 collation if the input is of char or varchar data types.”

I started experimenting with SQL 2022 with a default, US database. I ran this code:
```
SELECT N'Denver ' + NCHAR(0x1F601), DATABASEPROPERTYEX('sandbox', 'Collation')
```
That gave me unexpected results. The inputs aren’t char or varchar. They are NCHAR.

Strange. I’d have expected this to work. Let’s try the COLLATE clause. That should help.

It doesn’t.

One Solution

I decided to create a new database to test things. First, I ran this code to create a database using a UTF-8 collation:
```
CREATE DATABASE UnicodeTest COLLATE Latin1_General_100_CI_AS_SC_UTF8
```
Next, I tried my test. Same code as above, different database.

This works. I see my Unicode characters.

Why, I’m not sure. I would think that my requesting a collation for a query would work, but I see this in the docs, which notes this is for ORDER BY.

In the Write International T-SQL Statements doc, there is this:

I’m not sure what UCS-2 means when I’m querying in memory only, but apparently this matters.

An Explanation

The real answer is found in the NCHAR() docs. In here, the arguments section notes this:

The key is the Unicode value. NCHAR() handles up to 0xFFFF (4 Fs). My value is 0x1F40E (5 characters), so it’s out of range for the values that are handled with a non SC collation.

If I return to my Sandbox, non SC collation database, I can get Unicode characters, as long as they are below the FFFF threshhold.

A fun little experiment, where I learned something.

SQL New Blogger

This is a great example of my finding a problem, digging in, and solving it. Around some other work, this probably took me about 30 minutes to figure out with some reading and experimenting. Then about 15 minutes to write this post.

This is something you could easily do and showcase your knowledge as someone looking to learn and grow.
December 3, 2025
Finding the Last Last Name in SQL: #SQLNewBlogger
I wrote a piece on the new SUBSTRING in SQL Server 2025 and got asked a question. How do we get the last last name, such as only getting “Paolino” from “Miguel Angel Paolino”. This post will show how you can easily do this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

I have a set of names, like those in the Northwind.dbo.Customers table. I want to find the last names only, perhaps for a mailing, or maybe for a search box. I have names like these:

Notice line 80 above. There are three names here. In the US, we might consider this as a first, middle, and last names. In Spain, however, this might be a first name and two surnames. If I only wanted the last last name (Paolino), how can I get that?

One of the cool things about working with strings is that we can look at them a few ways, and we have a great T-SQL function that can help: REVERSE(). The last last name is really the first name in a reversed string.

Backwards, but we can fix that.

Let me build up a query. First, I’ll get the ContactName and then the First Name. I’ll use the Charindex to find a space and then assume everything before the space is the first name. That gives me this code:
```
SELECT
        ContactName,
        SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName
FROM dbo.Customers;
```
And these results. Notice I have the first names. This isn’t perfect, but it’s often works.

Now, let’s add the string reversed.
```
SELECT
        ContactName,
        SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName,
        REVERSE(ContactName) AS ReversedName
FROM dbo.Customers;
```
The results are interesting. Look at lines 79 and 80. The first name is the first word before a space. For the last name, it’s the first word before a space, but reversed. The first part of 79 is shpesoJ and the first part of 80 is oniloaP.

So let’s repeat our substring on the reversed string. Here’s new code:
```
SELECT
        ContactName,
        SUBSTRING(ContactName, 1, CHARINDEX(' ', ContactName)) AS ContactFirstName,
        SUBSTRING(REVERSE(ContactName), 1, CHARINDEX(' ', REVERSE(ContactName))) AS ReversedLastName
FROM dbo.Customers;
```
And look at the results. now my third column is the last name, just backwards.

Now we can wrap that last column in another REVERSE() and we get the results we want.

SQL New Blogger

This is a common type of task, and one that you might be asked in an interview, or as a part of a spec. This post only took about 10 minutes to write, with code, and if this were on your blog, I bet an interviewer would ask you how to do this.

Try to influence the interview and write your own post. Do some testing on performance as well, explore how to work with T-SQL to become better at it and showcase this to your next hiring manager.
November 19, 2025
SQL Server 2025 RegEx and AI
One of the language changes in SQL Server 2025 that I’ve seen a lot of people mention is the addition of RegEx functions to T-SQL. I decided to take a few minutes and try to examine how this feature works, and how I might use it. And more importantly, can AI help?

This is part of a series of experiments with AI systems.

Data with a Bit of a Pattern

One of the common things people use Regex for is validating email addresses.

I created a basic table in a database that looks like this:
```
CREATE TABLE customer

(
     customerid INT NOT NULL
         CONSTRAINT CustomerPK PRIMARY KEY,
     customeremail VARCHAR(200),
     validated TINYINT

)

GO
```
I then asked PromptAI to get me some test data like this:

I wasn’t connected to a database, but I still got code generated with insert statements. The AI also noted I had a “validated” column, and it populated that appropriately: 1 for good email, 0 for bad ones.

Once I run the inserts, I have data.

Now, can I check that the AI did this correctly?

REGEXP_LIKE

In SQL Server 2025, there are a number of regular expression functions, and REGEXP_LIKE is one of these. This function is designed to return a boolean if the string_expression matches the pattern_expression, where the latter is the regular expression.

Prompt sees this as a valid function for SQL Server 2025, which is great. I want to use the customeremail from the table as my string to check. For the regex, I need to create a regular expression, something I am not good at doing. I learned to do this at a very rudimentary level when writing Perl, but I’ve lost whatever little skill I used to have.

I saw an expression on StackOverflow for validating email. Let’s check if an AI can help.

In a few seconds, even on airport wi-fi (SFO as I write this), I get a response.

This looks like the expression from the SO answer, though much shorter. If you read SO, you know that the format isn’t as set and stable as we’d like. In any case, let’s see what this shows.

This is interesting. I get an error.

If I look at the docs, this says it returns a true/false, which I’d assume would convert to a 1/0, but let’s try an explicit case. When I do this, it works.

I can also do this in the WHERE clause, where the true/false thing just works. Let me move the function to the WHERE clause. If I do that, I have this code:
```
SELECT customerid,
        customeremail,
        validated

FROM dbo.customer

WHERE REGEXP_LIKE(customeremail, '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$') 
```
When I run this, I see what I expect:

Not a bad enhancement to the T-SQL language.

Summary

There’s a lot written on RegEx, so this post isn’t intended to delve deeply into what RegEx expressions you choose. Rather, I wanted to show the basics of this REGEXP_LIKE() function, and showcase one of the weird things I found when including this in the column list. I also haven’t looked at performance yet, though that certainly is something to be concerned about with larger datasets

There are other changes in SQL Server 2025, and I’ll try to examine some in the coming weeks.
November 12, 2025