Tag: T-SQL

  • Learning from Mistakes: T-SQL Tuesday #194

    We’re a week late, once again my fault. I was still coming out of the holidays and forgot to check on my host. Luckily, Louis Davidson (who did have Feb) agreed to go early. He has a nice invite, and I am glad to answer.

    This is the monthly blog party on something SQL Server/T-SQL/etc. related. I have about half of 2026 covered, but if you would like to host, I’d love to have you. Ping me on X/LinkedIn/BlueSky.

    A Mistake

    Since we aim for T-SQL, I decided to ping something I’ve done a number of times in T-SQL, and sometimes still break. However, a little testing has helped me (mostly) keep this from getting to production.

    Always have testing in place.

    I am good at T-SQL, but not amazing.  I learn things from others all the time, and these days, take help from AIs, though I do test and double check what they do.

    One of the places I’ve struggled with is with outer joins. Usually left/right outer joins where I am trying to get a list of things from a join, but filter out some of the missing items. Here’s an example from Northwind. I want a list of customers joined to orders, but I might have a way where customers filter out those who haven’t been charged freight. There’s likely some business reason, but it escapes me now.

    If I run this query, I get lots of stuff.

    2026-01_0109

    That doesn’t seem right. If I check, I see this:

    2026-01_0111

    What’s the problem here? Well, the main issue is one I keep doing, fortunately, I catch this. If I move the Freight IS NULL to  WHERE instead of the ON, it works. You can see this below.

    2026-01_0112

    If I see too much data, which can be hard to catch in large result sets, I can ask Prompt AI.

    2026-01_0113

    I get the response I’d expect from most AIs.

    2026-01_0114

    How do I test for this? Well, the best way is to have test coverage for queries. For example, I might build a test like this:

    EXEC tsqlt.NewTestClass @ClassName = N’QueryTests’ — nvarchar(max)
    go

    CREATE OR ALTER PROCEDURE [QueryTests].[TestCustomersWithoutOrders]
    AS
    BEGIN
    — Arrange
    — Create temporary table to hold expected results
    DECLARE @Expected TABLE
    (
    CustomerID nchar(5)
    )

    — Insert the expected result – customers with no orders
    INSERT INTO @Expected
    SELECT CustomerID
    FROM dbo.Customers
    WHERE CustomerID NOT IN
    (
    SELECT DISTINCT CustomerID FROM dbo.Orders WHERE CustomerID IS NOT NULL
    )

    — Act
    — Create temporary table to hold actual results
    DECLARE @Actual TABLE
    (
    CustomerID nchar(5)
    )

    — This should be the query that’s being tested
    INSERT INTO @Actual
    SELECT DISTINCT
    Customers.CustomerID
    FROM dbo.Customers
    LEFT OUTER JOIN dbo.Orders
    ON Orders.CustomerID = Customers.CustomerID
    WHERE Orders.CustomerID IS NULL

    — Assert
    — Check that we have exactly 2 results
    DECLARE @ActualCount INT =
    (
    SELECT COUNT(*)FROM @Actual
    )

    IF @ActualCount <> 2
    BEGIN
    EXEC tSQLt.Fail ‘Expected exactly 2 customers without orders, but got ‘,
    @ActualCount;
    RETURN;
    END

    — Check that we got the expected customers
    IF EXISTS
    (
    SELECT 1
    FROM @Expected e
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM @Actual a
    WHERE a.CustomerID = e.CustomerID
    )
    )
    OR EXISTS
    (
    SELECT 1
    FROM @Actual a
    WHERE NOT EXISTS
    (
    SELECT 1
    FROM @Expected e
    WHERE e.CustomerID = a.CustomerID
    )
    )
    BEGIN
    EXEC tSQLt.Fail ‘The actual set of customers without orders does not match the expected set.’;
    END
    END;

    That’s a lot of code, but I can see it works. I get two customers back, which is what I expect. Lines 53-58 have my query being tested above. If I run the test, it passes.

    2026-01_0115

    If I change those lines to put the filter in the ON clause (and remove WHERE), it fails.

    2026-01_0116

    Ideally I’d have this in a proc so I can change/tune this and compare plans, run tests easily, etc.

    This is a mistake I still make at times today, albeit rarely. Now I write some tests to look for my mistake. Maybe that’s the thing I’ve learned the most: have tests for my code.

  • 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.

  • 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.”

    2025-12_0088

    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.

    2025-12_0089

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

    It doesn’t.

    2025-12_0091

    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.

    2025-12_0093

    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.

    2025-12_0094

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

    2025-12_0095

    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:

    2025-12_0096

    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.

    2025-12_0097

    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.

  • 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:

    2025-11_0155

    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.

    2025-11_0157

    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.

    2025-11_0159

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

    2025-11_0160

    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.