Testing is Becoming More Important

Posted on February 25, 2026 by way0utwest

Many of us know that testing our code is important. The adoption of unit testing by many software application developers as a normal course of business has dramatically improved the quality of applications. Mobile software, especially, has benefited from the requirement for most software to include, and constantly run, a suite of unit tests.

For database software, I find relatively few organizations formally test their database code. A few people have adopted tSQLt or the Microsoft Unit Testing Framework, but most don’t bother. In fact, many queries that are embedded in application code, or built by ORMs, aren’t tested beyond a developer looking at the results from their own (limited set of) test data. That often doesn’t catch errors until someone in production runs their application against a larger set of data.

What might be worse is that refactoring those queries might produce different results that aren’t tested against regressions.

In this new age of AI-assisted coding, testing is becoming more important. Grant wrote an interesting post on LinkedIn that discusses your job changing in the age of AI. You need to have more testing that ensures you validate code that the AI produces, which is going to be more important as the amount of code grows. AI will produce lots more code, and potentially, lots more poor code. We will need to ensure that the generated code  has some validation that the results are what we expect.

Unit tests help here, and while I know these can be tedious to write and maintain, this is a great use for AI assistance. Generating unit tests, with default data based on data in current tables, is something AI agents can do well. They can also use these to verify functionality as code is generated and refactored. Of course, humans still need to be in the loop as there are plenty of reports where AI Agents write tests that return success without actually testing code. This is something humans have done as well.

You need to validate the tests, and ensure your AI uses those tests to validate its work. Those tests can also be used by humans if they write code.

AI is an amazing tool, but like an intelligent, over-eager, junior developer, it needs clear communication and strong guidance.

And a little review of its work.

Steve Jones

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

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

Posted in Editorial | Tagged , , | Leave a comment

Identity Columns Can’t Be Updated: #SQLNewBlogger

Posted on February 25, 2026 by way0utwest

I’m not sure I knew identity column values could not be updated. I ran into this while trying to solve a problem recently and had to check the error I was getting. This post shows what happened.

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

Setup

A quick setup for you. I need to go to the store soon, so hence, here is my sample table (created and filled by SQL Prompt).

CREATE TABLE Vodka
( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  brandname VARCHAR(100) NOT NULL
  , rating TINYINT
);
INSERT INTO dbo.Vodka
(
    brandname,
    rating
)
VALUES
('Grey Goose', 9),
('Belvedere', 8),
('Absolut', 7),
('Smirnoff', 6),
('Stolichnaya', 8),
('Ketel One', 9),
('Tito''s', 8),
('Ciroc', 7),
('Skyy', 6),
('Russian Standard', 7););

I then tried this:

2026-02_0157

OK, what about IDENTITY_INSERT. I know this isn’t an insert, but I thought this “unlocked” the identity column. It doesn’t work.

2026-02_0158

I searched on MS Learn and found the UPDATE statement documentation. In here, you can see what it says below. I can’t do this.

2026-02_0159

The error reference provides no info, but apparently this isn’t a thing.

What’s amazing to me is that in 30 years either I’ve never done this, or I’ve rarely encountered it and forgotten. Either is possible.

In any case, if I want to change this, I likely need to “re-insert” the row with a new value (either take the seed or use identity_insert) and then delete the old one.

Crazy.

SQL New Blogger

I was testing something else and ran across this. I decided it’s a great showcase of me learning something and giving a workaround. I’ll show the workaround in another post, which is actually about the thing I was doing.

Of course, that post needs to change.

This took about 10 minutes to write.

Posted in Blog | Tagged , , | 1 Comment

Can You Let Go of Determinism

Posted on February 23, 2026 by way0utwest

Why do we reboot machines when something goes wrong?

I’m sure all have done it, and I would guess quite a few of you have found situations where this seems to fix issues, but there isn’t an underlying root cause that you can pinpoint.  This is a fairly accepted way of dealing with issues, but have you thought about why this is a way to solve some problems?

The main thing that a reboot does is return the system to a know starting state. It’s why quite a few people complain about some modern laptops and mobile devices because they avoid restarts and try to sleep/wake instead. Most software expects to work on a stateless machine, so restarts help find a known good state.

Coincidentally, this is why databases are so hard for many people, especially software developers. Databases are state machines, which are inherently more complex than stateless ones. However, that’s not the thing I want to discuss.

If you think about code you’ve written and problems solved, which datatypes cause the most headaches and challenges? Which types of data are most difficult to deal with? There might be a variety of answers, but one of the most common ones is datetime data. The main reason? It’s not deterministic in many cases when we deal with calculations in real time. This data ages poorly and it’s hard to even test. By the time we’ve restored data from production, invariably our test data is old. We can de-age it (make it newer), but still, testing this data based on what happened yesterday is often hard.

This has been on my mind as another modern technology has similar characteristics. AI LLMs are often not deterministic. The same prompt might not produce the same response, and like SQL Server execution plans, even small changes in the input can affect the output.

That can be maddening to many of us, as we often want a reproduction of a problem to solve it. I ask for this from clients, Microsoft asks for it when I send them an issue, and most software developers want to be able to reproduce a problem on their machines. Or they often struggle to fix a bug.

In this new AI world, is determinism something most of us can hold loosely? It’s a good question as many of us struggle with AI when we don’t get the response we expect, or even a good response. Worse, we might get varying levels of quality code back from the same models. I have found that an experiment I conduct sometimes cannot be reproduced with any accuracy.

And sometimes it works the exact same way the second and third times I conduct the experiment.

Humans are not deterministic. Many of us know someone that is very reliable and can predict how they react most of the time. Most, however, isn’t a characteristic of determinism. I guess in that sense, humans are a state machine as well, one that is constantly evolving and different every day.

I find that success with modern AI LLMs requires me to accept some level of determinism and flow with it. I need to not expect the results to be perfect, and either massage the way I express the problem or give up. I’ve written it before, but I think learning when to give up on an LLM and just do the work yourself is a key skill for technologists.

Maybe for anyone using LLMs.

So, as you think about the future, are you prepared for one without determinism?

Steve Jones

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

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

Posted in Editorial | Tagged | 4 Comments

Rolling Back a Broken Release

Posted on February 23, 2026 by way0utwest

We had an interesting discussion about deployments in databases and how you go forward or back from the point when you discover a problem. You can watch the episode and see what you think, but one thing that Pat asked was about rolling back a broken release.

I’ve seen a few broken releases that were rolled back immediately in my career. However, in a lot of cases, I’ve also been a part of semi-failed releases where we had to roll things forward.

I learned early on to smoke test the system post-deployment. Either get an account, or run known queries after applying a patch to ensure things worked BEFORE I let someone know the deployment was complete.

In one case, we applied a patch, restarted the application and started receiving errors immediately. We knew then that whatever things had changed, those changes were not in sync with the application. In this case, the application code and the database code had a small typo in a name, but late at night, we didn’t realize it was this simple.

In the days of outage windows, we couldn’t debug for long, so I decided immediately that getting back online was important. We replaced the new .exe with the old one and I looked at each of the database commands and wrote a reversing one to reset the database. Since the system was down, there weren’t any data changes.

In recent years, a few clients have had an easier time as they use feature flags to enable new functionality. When they’ve had an issue like the wrong name in code, they just flip the toggle to disable the feature. This rolls back the code.

I highly recommend using feature flags to anyone working with database software changes. Coupled with zero-downtime architectures for database changes, this lets us rollback things quickly.

Posted in Blog | Tagged , , | 2 Comments