Voice of the DBA

Tag: T-SQL Tuesday

Impactful Sessions I’ve Seen: T-SQL Tuesday #196

This month I’m thrilled that Steve Hughes is hosting. I’ve read this Data on Wheels blog for a long time and I appreciate all that he does, especially given his struggles with ALS. I kicked myself a few times for not asking him to host earlier, but I’m glad I finally did.

I also was honored to meet him a few weeks ago and spend a little time with him.

With that, let’s get to Steve’s invitation.

Lasting Memories from Events

These days I don’t go to a lot of sessions at events. It’s not that I can’t learn from others, or be inspired, but I find myself often viewing the time spent with others as more valuable. I can (and often do) work through articles, tutorials, and other resources online. For me, the time talking to others if more valuable than sitting in sessions.

However, I wasn’t always like that. Early on in my career, and even throughout, I went to a lot of sessions to learn things. Over the years, a few things have stood out to me. There is some recency bias here, and I’m sure I’m forgetting about some great sessions along the way. I don’t always remember titles, so forgive if I’ve made a mistake.

Tempdb in Early SQL Server – This was at the very first PASS Summit in Chicago, where Kalen Delaney have a talk on tempdb. I remember this because we had lots of contention in tempdb on SQL Server 6.5 and I was struggling. Finding solutions was one of the main reasons I got my boss to pay for the conference. I know I learned a few things and worked to restructure some queries after this. I even got to ask Kalen a question, which was a thrill for me.

Reporting Services CI – I think this was a Nigel Sammy session, but it might have been Roy Ernest or another friend. I was working to get DevOps style checks for SQL code at the time and I wondered how someone would do this for SSRS. It was an interesting session as it reaffirmed a lot of my guesses for how to approach this, but taught me an interesting way to try and verify parts of reports while ignoring others. The session focused on ensuring your clients were happy with report data more than visual layout.

The BI Power Hour – I went to a few of these, but the first one was with Donald Farmer, Euan Garden, and others at TechEd in 2003 or 2004. It was wildly entertaining and I appreciated the humorous approach to showing ways to use the BI platform. While I am not quite as entertaining, I try to add a few minor funny things in sessions.

Basic Backup for SQL Server – Sean McCown gave this at a few SQL Saturdays. I am not sure if I saw him in Dallas or elsewhere, but I think I was sitting in the room and decided not to go anywhere. Instead, I was curious what Sean might cover that I didn’t know. While I knew most of the material, there were a few tweaks in settings that made a different to performance. This session reminded me there is always more to learn, even when you think you know a topic well.

Clustering in 60 Minutes – Early in the SQL Server Central days, Brian was doing a lot of HA for his company. He presented this session at a PASS Summit where he built a SQL Server 7 or 2000 cluster live onstage in 60 minutes. It was cool to see and it reminded me that I should practice my demos and make them smooth.

Live Encryption Changes – Simon Sabin gave an interesting talk at SQL Bits where he showed a live app running against his database and how he could implement encryption without breaking the app. The app just ran a workload against his database in a loop. He made changes to the db in the background without causing errors, which was cool. I enjoyed learning the encryption techniques, but more this inspired my Architecting Zero Downtime Deployments, where I wrote my own app to do this.

There are plenty more, but I’ll stop here.

There’s always a lot to learn, and going to sessions is a great way to get inspired and help you build your own solutions.

April 14, 2026
T-SQL Tuesday #196: Taking Risks

This month we have a new host, James Serra. I’ve been trying to find new hosts over the last few years to keep this party going and to expand the ways in which people look at database work.

There are definitely less bloggers, but in the age of AI, I think the more you can stand out and show that you add value, that you think about things, that you can spot AI mistakes, the better off you are, so keep blogging an encourage others to do so.

This is a great invite, and I’ll give you my response below.

A Big Leap

I had changed jobs a few times after I moved to Colorado. I worked at an established small company, which was still a bit of a startup, and after sleeping in my office 8-10 times in a year, I looked for another job. Another startup, which failed, left me looking again. I got a job at JD Edwards, which turned into PeopleSoft after an acquisition. I was promoted, and in a good place.

At the time, I’d been running SQL Server Central on the side with Andy and Brian for a couple of years. We were making it work, but it was stressful in that we spent time at night, on weekends, and during breaks keeping it growing. We were feeling the stress and decided someone needed to work at it fulltime. We’d made a big sale, so we had some cash to give us stability, but if things went poorly, this was about a half a year’s salary for any of us.

We debated it a bit and I decided to make the leap to working for the company fulltime. I took a small pay cut to do so, but we expected a monthly dividends to make up for most of the decrease in pay. Not all, but most. The main reason I decided to make the leap was that my wife was working fulltime and provided our health benefits. That was a cost Andy or Brian would have needed to shoulder themselves.

This was a risk. At the time, I worried about my skillset. Would they atrophy? If this didn’t work, could I go back to work as a consultant or fulltime DBA? Would I miss out on the learnings and growth that come from being involved fulltime in projects for an organization?

Looking back, it doesn’t seem like a big risk, but at the time, in the 2003 timeframe, it felt like a major leap away from the security and stability of corporate work. Sure, I’d changed jobs in the past, but I always had a strong track record of delivering results in a position. I was giving some of that up and the longer I was away, the more I was worried about coming back.

Over time I realized that a lot of what I do here with testing scenarios, mocking situations, and working through the challenges people face is the same type of work that I would do in a corporate environment. I don’t have the pressure from a manager, but I often put that on myself, so it’s very similar.

This was a calculated risk, but still a work. Fortunately, it was a one that worked out well.

March 10, 2026
Aging Code for T-SQL Tuesday #195

It’s that time of the month again, with T-SQL Tuesday coming along. I managed to not forget about this and checked with the host. He had an issue, but fortunately I got a friend to step up.

This month Pat Wright has an interesting question, asking how your code has aged. He and I have had a few conversations lately about getting older and when I asked him to host, this was a perfect choice.

I’m definitely getting older, but what about my code?

25 Years Later

Actually a little more, but I wrote a series called “Tame Those Strings” for Swynk a long time ago. That became Database Journal, but during the switch, they stopped paying us authors. A few of us started SQL Server Central and we went live 25 years ago.

In that piece, I referenced the oldest article, which is Tame Those Strings Part 4 – Numeric Conversions. There’s also a part 3, but in those pieces, is that code still useful?

A bit.

These are basic articles looking at string functions that are still heavily in use today. The idea of cleaning phone numbers using REPLACE is still something we might do today. If we are using SQL Server 2025, there are additional functions, but I still see a lot of code that still use multiple CHARINDEX+SUBSTRING or REPLACE functions.

I asked the Prompt AI if it could do better.

It gave me two pieces of code. The first is nested REPLACE() statements. This works, but I find this hard to read. I’d rather have separate statements, for ease of maintenance.

The second is a single statement, using a CASE and STUFF and XML to clean things. I like this, thought it’s a semi-complex way of doing things. However, it works.

Has the code aged well? I think it’s OK. There are better ways, as shown with the STUFF/XML version, which wouldn’t have worked in SQL Server 2000. Still, the use of REPLACE is a common technique still used today.

For part 4, with the use of LTRIM and STR(), today we have FORMAT, which is cleaner. However, it’s likely less performant. In a simple test of 500,000 values, the FORMAT takes over 600ms to return the results while my LTRIM/STR combination consistently runs in the 460ms or quicker range.

I think my code aged well.

February 10, 2026
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.

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

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.

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

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

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.

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

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.

January 20, 2026