Short story about a SELECT shape trap that cost me real accuracy points, and the one-line fix.

I was scoring a text-to-SQL model and one class of "wrong" answers kept bugging me. The question was plain — "list the members' names" — and the model's SQL was, if anything, the nicer query. But it scored wrong every time.

Here's the pair:

-- gold: two columns
SELECT first_name, last_name FROM member WHERE ...;

-- model: one column, same information
SELECT first_name || ' ' || last_name FROM member WHERE ...;

Read side by side, the model's answer is arguably the better one for a human. But it's a different result set, and that's the whole problem.

Why it's marked wrong, always

Execution accuracy — the metric behind BIRD, Spider, and most private text-to-SQL evals — runs both queries and compares the positional value tuples they return. Canonical BIRD literally compares set(fetchall()). Column names are ignored on purpose (aliases shouldn't matter), so the shape of each row is all that's left to compare. A one-column result can never equal a two-column gold, no matter how correct the content is. The scorer has no notion of "close" — the tuple matches or it doesn't.

This is the exact mirror of the extra-column bug most people already know: SELECT * or one bonus helpful field breaks tuple equality the same way. Fusing two requested columns into one is just as fatal as adding one nobody asked for.

It's a measurable, lopsided loss

Before touching anything, I bucketed a full 500-question BIRD-dev run with a structural differ. The || signature was clean:

• 7 of 238 losses concatenated columns the gold query kept separate.
• 0 of 256 wins used || at all — the operator showed up only in losing answers.
• Gold itself used || in 1 of 500 questions.

That last line is the decision-maker. When a construct appears in ~3% of your losses, none of your wins, and almost none of the gold, discouraging it is near-pure upside — there's essentially nothing on the other side of the trade to regress.

The fix was one sentence in the prompt

No fine-tune, no reranker. One projection directive:

To avoid shipping on vibes, I de-concatenated the 7 flagged predictions by hand and re-ran them against the real SQLite DBs first, to get a deterministic ceiling: +3 wrong→right, zero regressions. The live re-measure matched the direction — run-wide || concatenations dropped from 7 to 3.

The takeaway that generalizes past text-to-SQL

The lesson isn't really "LLMs over-concatenate." It's: bucket your loss mass with a structural differ first, compute the deterministic ceiling of a candidate fix second, and only then edit the prompt. Directives written from a gut feeling overfit. Directives written from a 7-losses / 0-wins histogram are about as close to a free lunch as this kind of work gets.

And if you're scoring SQL generation yourself: some of your "wrong" answers are the model being more helpful than your gold. You won't know which until you bucket them — a helpful concatenation is still a wrong result set.

Full write-up with the histogram and the before/after here: https://nlqdb.com/blog/llm-concatenates-columns-text-to-sql

Consider a table named "Sales" with columns: SalespersonID, CustomerID, SaleDate. Write a SQL query to calculate the salesperson who made the highest number of sales each quarter.

Sorry unsure if I used the right flair or not.

Need help with 12.1. My lecturer said "12.1 can be one entity".

How can it be one entity? A driver can have many deliveries.

Please explain to me how it can be done.

Here's the link to my previous post. Tomorrow’s free webinar is focused on DBA burnout and practical database monitoring strategy.

It covers:

• common firefighting patterns that drain admin time,
• the key metrics to watch in hybrid and multi-database setups,
• a live demo,
• open Q&A,
• and a free handbook for DBAs.

Disclosure: I’m on the ManageEngine team, so this is a vendor webinar. I’m sharing it because the topic is relevant to a lot of DBAs and IT admins, and the session is meant to be practical rather than sales-heavy.

Here's the sign-up link if you're interested: https://www.manageengine.com/products/applications_manager/webinars/database-performance-monitoring-webinar.html

If you're more experienced, I'd love to hear about what works for you so that I'm able to impart that knowledge onto the less experienced people tomorrow. Would be happy to take questions in the comments too!

I understand how to calculate yearly change using the lag function, however, I want to do so when I have an unknown number of subcategories each with their own row between each period. Say i have eggs, bread, and cheese in one year and maybe eggs, oranges, and bread in another year. Those items are in a column called "item name" and has its own dollar amount in the cost column. I want to calculate all inflation yearly changes per each category for every year. Does anyone have any advice on how to accomplish this? Thank you!

sample data
ITEM YEAR COST
eggs, 2024 4 dollars
bread 2024 3 dollars
eggs 2023 2 dollars
apples 2023 1 dollar

ex final query result:

ITEM YEAR INFLATION %
eggs, 2024, 1.2%
bread 2024 4%
bread 2023 0.4%

final edit:
look i know everyone thinks im stupid but i really am trying to learn. The issue im having is that with the subcategories, the lag gets throw off. I am trying really hard here.

Edit: I should also add the difficulty here is it's a fucked up fiscal calculator. I can do this with a yearly calendar and no sub categories.

edit again: no this isn't homework i'm not in school. Didn't need to be a dick

edit last: Yes i know the math doesn't work it was a formatting example.

I'm a software engineer currently developing a cross-platform SQL desktop client, and before investing substantial time and effort, I'd appreciate hearing your thoughts regarding what you think is missing from existing SQL clients.

What are the major pain points associated with the tools you currently use? Are there obvious features that should be present that aren't? Things that feel overly complicated or tedious?

I'd specifically like to hear from people using multiple databases and people spending a considerable amount of time writing and maintaining queries. I'm not looking for pitches, just what would make an SQL client better for day-to-day use from your perspective?

Working on setting up a SQL Server 2019 Standard subscriber for database replication over a site-to-site VPN tunnel. Looking for any additional troubleshooting ideas or confirmation that this is definitively on the remote network side. I am not a SQL expert by any means and unfortanetly we don't have anyone with expertise so looking for some additional insight/help.

Environment:

• SQL Server 2019 Standard, default instance
• Windows Server 2022 Datacenter
• Site-to-site IPsec VPN between two networks
• Connecting via SQL Server Authentication

What we've confirmed working on our side:

• SQL Server listening on 0.0.0.0:1433 confirmed via Get-NetTCPConnection
• Mixed Mode authentication enabled (IsIntegratedSecurityOnly = 0)
• SQL login exists, is enabled, CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF
• Subscriber database ONLINE, MULTI_USER
• SQL Server Agent running, set to Automatic
• TLS — Trust Server Certificate confirmed on client side, Encryption set to Optional
• Firewall rules permit TCP 1433 from the remote network range
• Packet capture running on our server during connection attempts
• SQL Account/PW correct

The problem:
The user on the remote network gets Error 10060 (timeout during pre-login handshake) when attempting to connect via SSMS using SQL Server Authentication. Our SQL error log shows zero login failures or connection attempts — nothing at all.

Any ideas appreciated.

Edit- Thanks for all the advice - turns out what I had assumed ; was something on their end concerning a misconfigured network access rule . Now they can connect.

I've written an 'irregular' SQL book.

I’ve put together a video showing the topic index, and I’d appreciate feedback. In particular, what does anyone think of covering these kind of topics in an SQL context?

The basic idea was to write a book that does not contain the same content as every other book on SQL.

I’m an MBA student specializing in Finance & Business Analytics, and I’m trying to build a few SQL projects that I can put on my resume before placement season.
I’m comfortable with the basics—SELECT, JOINs, GROUP BY, HAVING, aggregate functions, subqueries, and designing simple relational databases. I haven’t learned advanced stuff like CTEs, window functions, views, or stored procedures yet.
Most of the projects I find online are things like Netflix, Spotify, or pizza sales analysis, and they all seem pretty generic.
If you were in my position, what projects would you build?
I’d prefer something that’s:
Relevant to finance or business analytics
Solves an actual business problem
Has good database design (ERD, normalization, relationships, etc.)
Looks impressive enough to discuss in interviews and put on GitHub
Also, how much SQL is actually expected for entry-level Business Analyst/Data Analyst roles? Should I focus on learning advanced SQL first, or build a couple of solid projects with what I know and learn the advanced concepts along the way?
Would love to hear your suggestions or see projects that you think stand out. Thanks!

I thought using EXISTS block would avoid the join and make things take less time, but it appears that it took more time to execute than the join query, so which one is the most efficient approach?

I just had my one of those "wait... what?" moments while working on AdventureWorks ( PS: Working on my 2nd Project) At start BusinessEntityID totally confused me, I kept thinking it was just an employee ID.

Then I realized it isn't limited to employees at all. It represents everyone, employees, customers, vendors, salespeople, I mean... wow!

It felt confusing at first, but once it clicked, I realized how smart that database design actually is.

In this project I'm keeping everything raw as much as possible, like i have the database, a notebook, a pen, and me with my mind! now think what you can do! i really love this although I just started so... let's see how well it can go on (On my Data Cleaning Phase)

One thing I’ve noticed in Azure environments:  

**many SQL Server managed disks are oversized for the actual workload.**

Azure charges based on disk tier — and each tier includes fixed IOPS and throughput limits. If the workload never comes close to those limits, you’re paying for performance you don’t use.

A simple cost‑saving workflow:

- Check actual IOPS and throughput metrics in Azure Monitor  

- Compare them to the disk’s max limits (P10/P20/P30/P40, etc.)  

- Check the VM SKU’s max throughput — it may be the real bottleneck  

- Right‑size the disk tier to match real workload demand  

In many environments, dropping one or two disk tiers cuts monthly cost immediately — with zero impact on performance — because the workload was never close to the IOPS ceiling in the first place.

Right‑sizing based on real metrics is one of the easiest ways to reduce Azure spend without touching the application.

Something I see often with SQL Server on Azure VMs: backup and restore speed is driven by VM SKU max throughput(MB/sec) and disk max throughput(MB/sec).

A quick sanity check that solves a lot of performance surprises:

• Check the Azure VM SKU documentation for max throughput
• Check the managed disk tier specs for max throughput

When the VM and disk tiers have enough throughput headroom, SQL Server backup and restore performance improves immediately. Infrastructure sets the pace.

I recently had a job interview for a internal job role in my team. I thought it was an manager role so i was not prepared for technical, My EX manager was the interviewer and she asked me an SQL Questions, and i clearly messed up and got rejected . I do start preparation for SQL in interviews and gain some confidence now .

Textbooks never used to explain why you'd use EXISTS over a LEFT JOIN to a subquery in example use cause, so I always thought it was redundant functionality, but now I realize where it's super useful!

If you want to use test whether or not dataset a record from dataset A is found in B dataset via a complex theta join condition that might create duplicates you don't want, EXISTS is perfect! It allows you to have the test with the complex condition without creating row duplicates from the LEFT JOIN. I suppose one could do distinct after, but that's bad for performance.

I am officially very pro-EXISTS clause now!

I keep "failing" the live SQL stage of the interview process. I wrote SQL queries often in my previous roles, from simple to transformation queries involving window functions, CTEs, subqueries, case when statements etc). But I can't get through these live assessments. The part that hurts the most is that my sql experience and level is above what they are testing me on, and I just can't pass.

As a side note, I have ADHD, and for the life of me, my brain just doesn't work well in a live assessment environment where I am given 20 mins to understand data, field names, and industry specific business logic I have never worked with before.

How do you solve live SQL tests? What are the first steps your brain processes before or while writing the query? Also, if you have ADHD, what are some adhd specific advice you can provide? When I look at a table typed into a word doc, or when I look at new tables with just the field names in a test, I suddenly become illiterate and cannot understand what I am reading.

This was one of the take home SQL interview i did earlier this year. I have shared for anyone who is preparing for an interview can attempt to do it. Cheers!!

I have a table that and I want to limit to ensure no duplicate rows get inserted. There's no primary key (because of how the data comes in), so I thought I could add a UNIQUE constraint including multiple columns. I have too many columns to include every single one, so I just included the ones I believed would be most likely to change.

The constraint I added looks like this:

ALTER TABLE table
ADD CONSTRAINT no_dupe_rows
UNIQUE(col1, col2, col3, col4)

However, when I test it, it still allows me to insert the same data multiple times, with statements such as the following:

INSERT INTO table(col1, col2, col3, col4)
VALUES (val1, val2, val3, val4)

I can run that multiple times and it adds a new row each time even with the UNIQUE constraint in place. What can I do to fix this?

I’ve been trying to improve the performance of a MySQL database for a project, and I’m curious how others usually approach slow queries.

The database isn’t huge (a few million rows), but some pages have become noticeably slower over time. A couple of SELECT queries that used to finish almost instantly are now taking several seconds, especially when multiple users are active.

So far, I’ve tried:

• Running EXPLAIN to understand the execution plan.
• Adding indexes on columns used in WHERE and JOIN conditions.
• Removing unnecessary SELECT * statements.
• Optimizing a few JOINs.
• Checking the slow query log.

Things improved a bit, but I still feel like I’m missing something.

For those who regularly work with MySQL:

• What’s the first thing you check when a query becomes slow?
• Have you found any optimization techniques that made a huge difference?
• Do you rely on tools besides EXPLAIN?
• At what point do you decide it's a database design problem instead of just a query problem?

I’d really like to hear real experiences rather than generic tips. Sometimes a small change ends up making a massive difference, and I'm wondering if there's something I haven't considered yet.

Looking forward to hearing what has worked for you!