Voice of the DBA

Tag: AI

Can You Let Go of Determinism

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.

February 23, 2026
RANK() vs DENSE_RANK(): #SQLNewBlogger

I haven’t done one of these in awhile, but I saw an article recently about this and decided to explain it to myself, but in a slightly different way. You’ll see how I checked on RANK() vs DENSE_RANK() below.

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

Getting Started

Imagine you’ve never used these functions, but you need to rank some data. Let’s say that you have a series of things that measure sale and you want to know who has done the best job this week. I’ll start with AdventureWorks, and we’ll assume it’s June 2011. If I look at some data from that database, I see this. Order Headers and Order Details from that range.

Who are the top 5 salespeople?

I put this into SQL Prompt AI: who are the top 5 salespeople with ranking for the first week of june 2011. Give me the salespersonid, the rank and sales total

I got this code, though with 2011-06-01 as the first date, which didn’t work as there were no orders that week. I adjusted this back to May 31. You can see this uses RANK() as the aggregate.

I then asked about DENSE_RANK, because I heard about it and might want to use it.

Prompt adjusted the query to add DENSE_RANK, but I then clicked the “add explanation” next prompt prompt (is that a thing now?)

I now see an explanation, though in this example, this doesn’t help.

I then asked Prompt to help me understand.

I got back this code. You can see in the results, that both show me the ties for 2nd with people that have the same sales totals. However, with RANK, we jump to 5, since there are 3 people tied at 2 (presumbly taking 2,3,4th place). The dense ranking goes from the ties at 2 to 3.

I asked for an explanation:

I got this. This is the text from SQL Prompt AI (in italics):

# RANK vs DENSE_RANK: When to Use Each

The key difference between RANK and DENSE_RANK is how they handle ties:

## RANK
– Assigns the same rank to ties
– Skips the next rank(s) after ties
– Creates “gaps” in the ranking sequence
– Example: 1, 2, 2, 4, 5 (note the missing 3)

## DENSE_RANK
– Also assigns the same rank to ties
– Does NOT skip ranks after ties
– Creates a “dense” or consecutive ranking without gaps
– Example: 1, 2, 2, 3, 4 (no gaps)

## When to use DENSE_RANK:
1. **Consecutive Ranking Systems**: When you need a continuous sequence without gaps, such as in competitions where tied participants should receive the same position, but the next participant should be ranked immediately after.

2. **Top N Analysis**: When identifying items in the “top 10” or similar groups, DENSE_RANK ensures you get exactly 10 distinct ranks regardless of ties.

3. **Percentile Calculations**: When calculating percentiles or quartiles where a continuous ranking is needed.

4. **Educational Grading**: When multiple students with the same score should receive the same rank, but the next student should be ranked immediately after.

## When to use RANK:
1. **Traditional Competition Standings**: Olympic medals and many sports competitions use this approach – if two athletes tie for first place, the next finisher gets bronze (3rd place, not 2nd).

2. **Statistical Analysis**: When you need to preserve the mathematical properties of ranks where the sum of all ranks should equal the sum of consecutive integers.

3. **Scenario Where “Skipped” Positions Matter**: When it’s important to account for how many entities are ahead of each entity.

Your example query perfectly demonstrates this difference:
– Employees with $145,000 all get rank 2 with DENSE_RANK
– With RANK, they get position 2, but the next rank is 5 (skipping 3 and 4) because there are three employees sharing rank 2

Each function has its appropriate use cases depending on whether you want a continuous ranking sequence or one that accounts for the number of entities at each rank level.

What I Learned Here

I knew most of this, but in terms of trying to understand better where/why to use each, the explanation made sense. I hadn’t thought about the Educational Grading for DENSE_RANK, but that makes sense. I knew the others, and often that the TOP N needs the correct number of rankings.

For RANK, we use the competition ranking with volleyball, so I see that all the time, but I don’t do a lot of statistical analysis where this has come up, but it’s good to keep it in mind.

To me, I often go back to the client, or think about both of these when I rank things. I will do what Prompt AI did and put both in a query, see the differences and then decide (or let someone else decide) how to present the ranking data.

SQL New Blogger

When I started to explain this, I first opened the DOC pages and was going to use those to write this and thought, this is a good place to test AI models and see. I took a different tact and incorporated some AI into my work, because that’s where the world is going. Like it or not.

This went faster with AI, and less cognitive load from me. I wrote this post, but I used AI to help set things up, generate code, and get me there quicker. You could do the same thing and use a blog to showcase that you’re learning how AI is a tool you can use.

SQL Prompt can help you learn more about your code, in addition to all the cool time saving features. Give it a try today.

FWIW, I asked CoPilot the same query and got an answer (0 people), without code. When I asked for code, I did get it, but not quite what I wanted.

February 18, 2026
Claude AI Convinced Me Not to Build an iPad App

I coach volleyball and I do a lot of stat stuff on paper. I decided recently to see if I could find a way to more easily automate things. I’ve tried a few apps on the iPad, but they all have too many restrictions and they are hard to use in the heat of the moment. Paper and pencil have been simple, reliable, and they let me fix something easily.

However.

Paper takes some focus, and it’s hard to quickly summarize things. I wanted to make my own app, and Claude convinced me not to do this.

Read on.

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

A Simple Prompt

I’ve been kicking this around, but I never seem to have time. Listening to podcasts, reading articles, and seeing other experiments is slowly getting me to just try things. I started with this prompt, intending to give this 10 minutes out of my day.

how hard is it to build an app for an ipad

I know some of the answers, but you can look at the image below for what Claude gave me.

I like the conversational nature of this, so I answered with this:

a simple app to track some data entry. Can you build an app for an iPad?

I was just looking for help in my thought process here and trying to scope the work. I’m sure Claude Code could knock this out, but I’m experimenting. This was Claude’s response.

That’s great news. I don’t need a native app, and in fact, that’s likely a lot of overhead registering on a store or playing the sideload game, sending updates slowly, etc. With cloud file sync, I could easily have this data available elsewhere anyway.

This is the type of advice I’d get from some friends. Others would relish the chance to build an app and make this more complex. I’ve certainly seen some other Vb coaches on FB create complex systems.

My answer:

volleyball stats. Display a list of players in a 4×3 grid, like the image. Each player’s name is customizable. A date, goal, and time can be entered. For each player, display 4 buttons: 0,1,2,3 which are used to rate a pass. There should be a real time calculation of the average pass score for a player (sum of items/ attempts). Save this to a text file with the datetime data entry was completed.

I meant to upload the image below, but forgot. For context, I print this out and then as kids pass balls, I write 0, 1, 2, 3 for each pass. After we’re done (usually xx passes or time), I quickly compute an average. You can see below, Eliza gets a 3 and Ella gets a 2.

That takes time, so I wondered what Claude would say. This is the response, which took about 5 minutes.

I downloaded the file and opened it and saw this, or most of this. I updated the player names and they’re saved in the HTML.

I can click around and it logs attempts and calculates an average in real time. If I download the data, I get a text report of what happened.

If you want to try it yourself, look in my repo: https://github.com/way0utwest/AIExperiments/tree/main/VolleyballPassingStats

I asked for a few changes, and then I asked for a log of the session. That’s the readme in the repo.

To me, this was something I’ve put off for a few years, not wanting to get caught up in a project like this when I could easily just use paper.

Now I have a new toy that I’ll use at the next practice. Plus, I’ve amazed myself at how easy this was.

Be curious, try things, ask for help on a task or project you’ve put off. GenAI is amazing.

Other AIs

I tried this on a local Gemma3 model and it was very slow and produced a single entry box. to me, this wasn’t worth the time or effort.

I tried this in Copilot (in VS Code) with the Sonnet model, and it wanted to make me an xCode app right away with a SwiftUI. I could get it to produce something like what I got below, but it wasn’t as helpful as Claude.

Video Walkthrough

You can see some of this live in this video.

February 9, 2026
Expensive CPUs

There have been a lot of features added to the SQL Server platform over the years. Several of these features let us perform functions that are beyond what a database has traditionally been designed to handle. SQL Server has had the ability to send emails, execute Python/R/etc. code, and in SQL Server 2025, we can call REST endpoints.

Quite a few of these features (arguably) are more application-oriented than database-oriented. There’s nothing inherently wrong with having a server perform some of these functions, and there have been some very creative implementations using these features. I recently ran into one of these examples from Amy Abel, where she shows how to use the new REST endpoint feature to call an AI LLM to generate and send emails from your database server. That’s creative, and it’s reminiscent of the numerous examples from various experts over the years who demonstrate how these features can be used to accomplish a task.

However, these are examples. They work amazingly well with one user running a limited workload. This reminds me of many of the examples I’ve seen using the AI vector enhancement in SQL Server 2025 to create embeddings from string data using an LLM. That is interesting, but most of the examples show a trigger being used to update the encodings. Imagine users updating data and those triggers firing. Imagine a real workload and how often your users might update string data you want to use in an AI application, especially a RAG application. Think about how complex or long-running triggers in your applications now that can overload your system.

CPUs in database servers are expensive. The hardware isn’t more expensive, but the software is pricey. Standard Edition is limited to 24 or 32 cores (depending on version), and while Enterprise isn’t limited to any number of cores, the cost of each core is $$$$. Is it worth having those $$$$ cores sending emails or calling external services? Or would you be better offloading those calls to another server, like an app server, where the cost of the core is the hardware and a little .NET code running separately?

Many of us already struggle with the database server as a bottleneck for our application and workload. Scaling up our database systems is expensive and cumbersome. We struggle to get approval for larger VMs, and if we scale up in the cloud, it gets very expensive very fast. I’m not surprised that database vendors are happy to add these features as it increases the licensing cost for applications using them.

I know the majority of the cost of building applications is labor and software developers’ time. However, that’s changing with LLMs that can produce code cheaply. I keep seeing that the cost of writing code is approaching zero.

That’s not going to be true if you use LLMs trained on the example code that increases your database licensing cost. Then the cost might be higher than you expect.

Steve Jones

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

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

February 4, 2026