← Back to Dispatches

A SQL Query That Made Me Question All My Career Choices

War stories from twenty years of SQL Server — the bugs that crashed production at 3 AM, why vibe-coding makes it worse, and the lessons that leave permanent battle scars.

·6 min read
engineeringdatabasesSQL
sql query

Preamble

Every team lead eventually has to wrestle with coding standards. Years ago, when I was particularly obsessed with Microsoft SQL Server, I created mine. Fast forward to today, and I’m watching humans make the exact same mistakes. What’s alarming? With the rise of vibe-coding and vibe-coders, it’s reaching a whole new level of chaos.

Choose your mentors wisely. The difference between a senior engineer and a junior one isn’t just years – it’s the number of production systems they’ve accidentally crashed and learned from. Here are some never-aging pieces of wisdom from an old SQL warrior – lessons learned the hard way that left battle scars I wear proudly (and use in exhausting technical interviews, because you have to show candidates who’s boss, right? Mwuhahah).

Also, though this book came surprisingly unnoticed 20 years ago, it arrived as a holy revelation to me: Relational Database Index Design and the Optimizers. The fundamental concepts remain unchanged decades later.

Fun with Dates

sql query

Early in my career, I worked at a famous (or infamous) tobacco company selling products across Russia – 11 timezones, no joke. That’s where I earned my first battle scars learning to design, build, and support 24x7 mission-critical systems. Because leaving the entire Russian Far East without cigarettes on Monday morning? Well, you’d better have a one-way ticket with an open date to a country nobody knows exists.

This was also where I learned that career decisions are often made for you – usually around 3 AM when production is down and someone needs to be blamed.

Here’s one of the tables in the big-main-database:

create table dbo.sales_order_header (
		sales_order_id		int identity(1,1) primary key clustered
	,	sales_order_date	datetime	not null
	,	customer_id			int			not null
	,	sales_rep_id		int			not null
	,	sales_order_value	float		-- because you majored in applied math...
)

Once a month, when closing the books (hello, my dear finance friends), we needed all sales orders for a given month for a specified customer. The obvious approach? The BETWEEN operator exists for a reason, right?

declare @start_date		datetime = '20250901'
declare @end_date		datetime = '20250930'
declare @customer_id	int = 42
select *
from dbo.sales_order_header
where 
		sales_order_date	between @start_date and @end_date
	and customer_id			= @customer_id

Issue #1: The Missing Day

This one’s easy to spot, though similar code was recently generated by a modern smart LLM that’s otherwise become my best buddy. Since transaction dates include time portions, this obviously misses the last day of September.

The fix introduced by my predecessor (I’m certainly not admitting my own faults!) was adding the time portion: ‘20250930 23:59:59’. This almost always worked – until an especially hardworking sales rep made a sale at ‘20250930 23:59:59.593’ and created an improvised 35-hour beer-fueled team building for me and another poor soul.

Missing the days when employers brought beers right to your desk, along with a soldering iron to keep your motivation warm and up. After 24 hours of this marathon – beers, swears, and red eyes – we finally pinpointed the issue. My friend said, “Ha! Let’s just stomp on SQL’s throat.” And this masterpiece was born:

select *
from dbo.sales_order_header
where year(sales_order_date) = 2025
  and month(sales_order_date) = 9
  and customer_id = 42

I had déjà vu several months ago when a modern smart-ass LLM generated similar code…

Issue #2: The Production Crasher

This worked beautifully on the development database, so without much thought, it got rolled to production and completely crashed the SQL Server 2000 instance.

Time to learn how to read execution plans (and pray and check passport expiration dates). The reason? Not only did this generate a full scan, but the actual query had several more joins producing a hash join. The hash table couldn’t fit in memory, spilled to disk, exploded tempdb, occupied all available disk space, and crashed the system.

In Soviet Russia, query optimizer optimizes YOU

sql query

After a few more hours and mental promises to my grandma to become a surgeon (peacefully opening up unconscious humans as she always wanted me to do), the word “index” finally surfaced in our barely functioning brains. In those moments, you realize career pivots look very attractive – until you remember surgeons don’t get beer at their desks.

CREATE INDEX ix_sales_order_date ON sales_order_header(sales_order_date)

This worked surprisingly well on the smaller development database. We saw those magical words “index seek” in the execution plan and deployed to production – through a furious IT manager who was already pissed beyond limits. Deployment took a while and… nothing. Still a full scan (clustered index scan, more precisely).

Issue #3: The Non-SARGable Nightmare

Motivated by the literally hanging sword (and a soldering iron that was getting warmer and warmer) above our necks and the non-stop swearing IT manager, the abbreviation “SARGable” emerged from the dark corridors of unconsciousness, courtesy of the wonderful Kalen Delaney’s and Ken Henderson’s books.

SARGable (Search ARGument ABLE, because SQL people love inventing weird abbreviations that sound like a Scandinavian furniture brand) means the query optimizer can actually use your index. Because the date column was wrapped in a function, **the query engine couldn’t use it as a search predicate to traverse the index B-tree, forcing it to do a full scan instead – **completely ignoring our carefully crafted index like a teenager ignoring parental advice.

sql query

And advice from an old nag – learn foundational data structures in the era of vibe-coding. I mean, seriously, yes, go and code a B-tree from scratch without an LLM, like a psychopath! You know, just in case you need to impress someone at a party (spoiler: it won’t impress anyone, but at least you’ll understand why your query is crawling).

A new iteration was born that produced correct figures but still performed a full scan on the rapidly growing table (early 2000s Russia – the tobacco business was booming, because apparently lung health wasn’t trending yet):

select *
from dbo.sales_order_header
where sales_order_date >= '20250901'
  and sales_order_date < '20251001'
  and customer_id = @customer_id

This is where that masterpiece, Relational Database Index Design and the Optimizers (which I’d read months earlier while jumping between bus, train, and metro to get to work from Moscow suburbs – from the wonderful town of Zelenograd), introduced me to the concept of an ideal index. It must be:

  • Covering (contains all query fields, avoiding bookmark lookups in MS SQL 2000 terminology to the table or clustered index)
  • Properly ordered (fields in the right sequence)
  • Have a cool name (and something else that escapes me now) There’s a reason old nags like me hate seeing asterisks (*) in queries. The first thing I do (besides applying my weird formatting and ensuring all tables and columns have aliases) is expand it to a field list.

Since the accounting report needed only sales_order_date, customer_id, and sales_order_value, sorted by sales_order_date, here’s the final version:

CREATE INDEX ix_ideal_sales_order_header ON sales_order_header(customer_id, sales_order_date, sales_order_value)

Or even better for SQL Server 2005 and above:

CREATE INDEX ix_ideal_sales_order_header ON sales_order_header(customer_id, sales_order_date) INCLUDE(sales_order_value)

Why this field order in the index, by the way? customer_id, sales_order_date, sales_order_value. Test yourself and answer in comments.

declare @start_date		datetime = '20250901'
declare @end_date		datetime = '20251001'
declare @customer_id	int = 42

select
    soh.sales_order_id
,	soh.sales_order_date
,	soh.customer_id
,	sales_order_value
from dbo.sales_order_header soh
where soh.sales_order_date			>= @start_date
  and soh.sales_order_date			< @end_date
  and soh.customer_id				= @customer_id
sql query

The query was wrapped in a stored procedure, our accounting friends closed the September books, sales reps got their commissions, and my buddy and I could finally enjoy fresh air and silently drink beers under the gloomy Moscow fall sun, contemplating our career choices.

Until October closing, which revealed several other bombs planted in this solution… **Dare to find them, my dear readers? **The first index we created, we of course didn’t drop, just saying…

A Couple Last Suggestions from an Old Backend Warrior

sql query
  • Always code as if your code will be supported by a maniac who knows where you live. (© Tip of the day in an old version of Visual Studio)
  • Invest time into learning data structures, algorithms, and your favorite platform internals. Trust me, when production is burning at 3 AM, “I’ll just ask the LLM” won’t always cut it.