30 SQL Mistakes I Still See Developers Make (And the Production Nightmares They Caused Me) — After 25 Years Across SQL Server, Oracle, PostgreSQL, and MySQL
I’ve spent 25 years living inside databases. Not just reading docs — I mean: being called at 2:00 AM because payroll stopped, orders froze, replication broke, backups didn’t restore, a “quick hotfix” turned into a 9-hour outage, and somebody said “but it worked in dev.”
I worked with SQL Server, Oracle, PostgreSQL, and MySQL, and here’s the uncomfortable truth:
Different engines… same mistakes.
And most of them are not “syntax mistakes.” They’re mental model mistakes — not understanding what the optimizer is, how concurrency works, and what “performance” actually costs in production.
Below are the 30 biggest SQL mistakes I keep seeing — with the kind of pain they create in real life.
1) Treating the database like a dumb storage layer
This is the #1 root cause.
If you assume the database is just a place to “save things,” you’ll build systems that collapse under real workloads. A database is a query optimizer + concurrency engine + transaction system.
I’ve watched teams scale app servers x10 while the DB still burned because the real problem was bad queries + wrong indexes.
2) Writing SQL without understanding the optimizer (cost-based plans)
SQL is declarative. You say what you want, not how to get it.
The optimizer decides the “how” — and it decides based on:
- statistics
- cardinality estimates
- index options
- join strategies
- memory grants / workareas
- parallelism decisions
If you don’t read execution plans, you’re driving blind — at night — in the rain.
3) “SELECT *” in production code
It feels harmless until it isn’t.
- larger rows = more IO
- wider memory grants
- worse cache usage
- extra network payload
- surprise breaking changes when someone adds a column
I’ve seen SELECT * turn a fast query into a memory spill monster because someone added a large NVARCHAR(MAX) column.
4) Shipping features without indexes (then blaming “the database”)
A missing index is not a “nice-to-have.”
It’s the difference between an index seek and a full scan at scale.
The classic disaster:
A feature goes live, traffic spikes, and suddenly the DB is at 95% CPU.
You look at the plan and it’s scanning millions of rows because someone assumed indexes “are optional.”
5) Building indexes without understanding query patterns
The opposite problem is also common: “index everything.”
Too many indexes =
- slower inserts/updates
- higher fragmentation
- longer maintenance windows
- bigger storage costs
- more log generation
Indexes are not trophies. They’re a strategy.
6) Massive queries that try to do “everything in one shot”
Developers love mega-queries with 15 joins, nested CASE logic, and subqueries inside subqueries.
Sometimes the best performance tuning move is brutally simple:
Break it into steps.
Use temp tables (or staging) so the optimizer gets fresh stats and cleaner join choices.
I’ve personally seen a 40-second report drop to 1.5 seconds after splitting it into two phases.
7) Not using temp tables when they’re the right weapon
Temp tables aren’t “dirty.”
They’re often the difference between:
- a plan that guesses wrong
- a plan that can estimate correctly
Especially when the result set size changes wildly depending on filters.
8) Overusing CTEs like they’re always “materialized”
A lot of people think a CTE is like a temp table. Often it isn’t.
Many engines treat it like an inline view (depends on DB + version + query).
Result: repeated evaluation, bigger plans, worse performance.
9) Using cursors because “SQL can’t do it”
SQL can do it.
Most cursor logic is just set-based logic that someone didn’t learn yet.
Cursors are sometimes necessary — but “I didn’t feel like rewriting it” is not one of those reasons.
10) Abusing triggers until the database becomes haunted
Triggers feel elegant until your system becomes a crime scene:
- hidden side effects
- unexpected recursion
- write amplification
- debugging nightmares
- replication side effects
- “Why did this update touch 9 tables?”
I’ve walked into environments where nobody could explain why data changed — because the logic lived in triggers nobody reviewed in years.
11) Implicit conversions (the silent index killer)
This one is brutal because it looks innocent.
Example pattern (SQL Server-style logic, but the idea applies everywhere):
If the column is INT and you compare it to a string, the engine may convert the column — and destroy index usage.
That turns seeks into scans.
And scans turn OLTP into suffering.
12) Functions on the left side of predicates (non-sargable filters)
Example of pain:
WHERE YEAR(OrderDate) = 2025
That forces computation and often prevents efficient index usage.
Better:
WHERE OrderDate >= '2025-01-01'
AND OrderDate < '2026-01-01'
This single mistake has cost companies real money in cloud compute because queries never use the index they “swear exists.”
13) Explicit conversions on the left side of “=”
Same problem, different costume:
WHERE CONVERT(varchar(10), OrderDate, 120) = '2025-01-16'
You just killed your index.
14) ORDER BY everywhere (even when nobody needs sorting)
Sorting is expensive. It can spill to disk. It can blow memory.
I’ve seen APIs that added ORDER BY “just to be safe” and turned a high-throughput endpoint into a bottleneck.
Rule I teach:
Only sort when the consumer truly needs sorted results.
15) Using DISTINCT as a band-aid for bad joins
DISTINCT often means: “I don’t understand why I’m getting duplicates.”
Fix the join. Don’t hide the symptom.
16) Using UNION instead of UNION ALL
UNION often implies deduplication (sort/hash).
If you don’t need deduplication, you’re paying a cost for nothing.
17) Believing “the optimizer will figure it out”
Optimizers are good. They’re not magical.
If you feed the engine:
- ambiguous predicates
- missing stats
- broken indexes
- parameter-sensitive patterns
…it will still produce a plan — just not the plan you hoped for.
18) Not understanding statistics
If you don’t understand stats, you don’t understand query performance.
Stats tell the optimizer:
- distribution
- selectivity
- estimated row counts
Bad stats = bad estimates
Bad estimates = wrong join choices + wrong memory grants
Wrong memory grants = spills + slow queries + unstable performance
19) Never checking for plan regressions
Today it’s fast. Tomorrow it’s slow. Same query.
Welcome to:
- plan cache differences
- stats changes
- parameter sniffing (in engines that cache plans)
- data skew
If you don’t track plans, you’ll keep diagnosing the same “mystery slow query” forever.
20) Not understanding data skew (and why “average” lies)
A column can look fine on average and still be a killer.
Example: 95% of values are tiny, 5% are huge.
Your query performance becomes unpredictable depending on which value hits first.
This is where I’ve seen “random” timeouts that were not random at all.
21) Treating NULL like a normal value
NULL breaks assumptions. It changes predicate logic and index behavior.
If you don’t design around NULLs intentionally, you’ll ship bugs that only show up under real data.
22) Using OR conditions that destroy index usage
Sometimes OR is fine. Sometimes it forces scans.
In heavy OLTP, rewriting OR into UNION ALL can be dramatically faster.
23) Pagination that melts as the offset grows
The classic “OFFSET 100000” issue: it gets slower and slower as you go deeper.
Keyset pagination often performs far better when you can use it.
24) Not thinking about locking and blocking until production screams
This is where careers get humbled.
If you don’t understand:
- isolation levels
- lock granularity
- lock escalation
- hot spots
- transaction scope
…you will eventually build a system that blocks itself under load.
25) Long transactions “because it’s safer”
Long transactions aren’t safer. They are risk:
- bigger lock windows
- more blocking
- bigger rollback time
- log growth
- replication lag
I’ve seen a single long transaction freeze an entire system because it held locks across a “simple” batch process.
26) Ignoring deadlocks — and not implementing retry logic
Deadlocks happen. Especially in high concurrency.
The difference between a mature system and a fragile one is simple:
Mature systems expect deadlocks and retry safely.
27) “We’ll fix it later” backups (and never testing restores)
Backups that haven’t been restored are not backups.
They’re wishful thinking.
I’ve lived the nightmare:
Backups existed, jobs were “green,” and the restore failed when we needed it most.
28) No visibility: no Query Store, no slow query logs, no baselines
If you can’t measure performance, you can’t manage it.
You need:
- slow query capture
- plan history
- wait/lock visibility
- baselines
Otherwise every incident becomes guesswork.
29) Treating production like a test environment
Hot fixes without rollback plans. Schema changes at peak traffic. Index rebuilds at the wrong time.
Production is a living animal.
Respect it.
30) Thinking certifications matter more than real skill
I’m not anti-certification. I’m anti-illusion.
A certificate doesn’t prove you can:
- read plans
- diagnose blocking
- recover from corruption
- design indexes
- fix replication
- restore under pressure
The industry needs more people who can solve real problems, not just pass exams.
What I’d tell any developer who touches SQL
If you want to stand out fast:
- Learn execution plans
- Learn indexing strategy
- Learn statistics + cardinality estimation
- Learn concurrency basics
- Learn how to troubleshoot under pressure
- Stop guessing — measure everything
Because in the real world, the database is not a feature.
It’s the beating heart of the business.
🚀 Ready to boost your career in data?
👉 DBAcademy – DBA & Data Analyst Training
Over 1,300 lessons and 412 hours of exclusive content.
Includes subtitles in English, Spanish, and French.
🔗 https://filiado.wixsite.com/dbacademy
💡 Start learning today and become a highly in-demand data professional.
Share this content:
Post Comment