Lesson 5 of 5

Common Gotchas

The last stretch isn't new syntax to memorize — it's a handful of behavioral differences that don't show up until something works fine in development and then breaks (or, worse, silently returns wrong data) somewhere else: a different server, a stricter transaction mode, a query that relied on MySQL's looser rules. These are the ones that cost the most debugging time precisely because nothing throws an error.

💡 The Bottom Line

SQL Server table, column, and database names are case-insensitive by default almost everywhere (it depends on the collation, but the vast majority of installs use a case-insensitive one) — a huge relief coming from MySQL, where table-name case sensitivity depends on the underlying filesystem the server happens to run on. SQL Server has always enforced the strict ANSI GROUP BY rule — every non-aggregated column in the select list must appear in the GROUP BY clause — where older or more leniently configured MySQL servers would silently run the query and pick an arbitrary value instead. And SQL Server locks more aggressively by default than InnoDB's typical MVCC-heavy behavior, which means porting a MySQL app's transaction patterns as-is can introduce blocking that wasn't there before. Treat all of these as "runs fine until it doesn't."

Case sensitivity of table names

MySQL (on Linux)

SELECT * FROM Orders;   -- works
SELECT * FROM orders;   -- works only if the
                        -- table was created
                        -- as "orders"
SELECT * FROM ORDERS;   -- Error 1146:
                        -- table doesn't exist

SQL Server (the new equivalent)

SELECT * FROM Orders;
SELECT * FROM orders;
SELECT * FROM ORDERS;
-- all three work identically
-- (under the default,
-- case-insensitive collation)

📋 Key differences

MySQL delegates table-name case sensitivity to the filesystem it's running on: Linux filesystems are typically case-sensitive, so MySQL table names are case-sensitive there; Windows and (by default) macOS filesystems are case-insensitive, so the same MySQL install behaves differently depending only on the OS underneath it.
SQL Server's case sensitivity is a property of the database's collation, not the filesystem — the default collation on most installs (e.g. SQL_Latin1_General_CP1_CI_AS, note the CI for case-insensitive) makes table, column, and data comparisons case-insensitive consistently, regardless of what OS the server runs on or what filesystem backs it.
A database can be created with a case-sensitive collation (CS instead of CI) — it's just not the common default, so most MySQL developers moving to SQL Server will find this whole category of bug simply stops happening, rather than needing active management the way MySQL's filesystem-dependent behavior does.

⚠️ Gotcha

The relief cuts both ways: a MySQL team that deployed on Linux and disciplined itself into always using consistent lowercase table names can get complacent once they're on SQL Server, where the inconsistency never bit them in the first place. If that same database is later restored onto an instance or database explicitly configured with a case-sensitive collation (uncommon, but it happens — often for compatibility with a case-sensitive application layer), every query that relied on the default's leniency can suddenly start throwing "invalid object name" errors. Consistent casing is good discipline on both engines, even when SQL Server's default doesn't currently require it of you.

Locking and transaction behavior

MySQL's default storage engine, InnoDB, leans heavily on MVCC (multi-version concurrency control) under its default REPEATABLE READ isolation level — readers rarely block writers, and writers rarely block readers, because each transaction sees its own consistent snapshot. SQL Server's default isolation level, READ COMMITTED, is lock-based by default: a reader can be blocked by an uncommitted writer holding a lock on the same rows, something that essentially never happens under InnoDB's default MVCC behavior. A MySQL app ported as-is, with no changes to isolation level or locking hints, can introduce blocking and deadlocks that simply didn't exist under MySQL's defaults.

📋 Key differences

SQL Server does have a snapshot-style option — READ_COMMITTED_SNAPSHOT (a database-level setting) or the explicit SNAPSHOT isolation level — that gives MVCC-like behavior much closer to what MySQL developers already expect. It's off by default on-premises, though Azure SQL Database enables it by default.
Both engines support full ACID transactions on their respective default engines (InnoDB on MySQL, no engine choice at all on SQL Server — there's no MyISAM-style non-transactional table option to accidentally end up with).
Deadlocks are possible on both engines, but SQL Server's default lock-based READ COMMITTED makes read/write contention on hot rows more common than it typically is under MySQL's default MVCC-heavy isolation.

⚠️ Gotcha

A report-generating query that runs a long SELECT against a busy table works fine under MySQL's default isolation (readers don't block on writers) but starts timing out or blocking under SQL Server's default READ COMMITTED, because it's now waiting on locks held by concurrent writers. Enabling READ_COMMITTED_SNAPSHOT at the database level is the closest like-for-like fix if you want MySQL's non-blocking-read behavior back, without rewriting every query to add explicit isolation hints.

GROUP BY strictness

MySQL (older / lenient mode)

SELECT customer_id, order_date, COUNT(*)
FROM orders
GROUP BY customer_id;
-- historically: runs, picks an
-- arbitrary order_date value per group

SQL Server (the new equivalent)

SELECT CustomerId, OrderDate, COUNT(*)
FROM Orders
GROUP BY CustomerId;
-- Error: OrderDate is invalid in the
-- select list because it's not
-- contained in an aggregate function
-- or the GROUP BY clause

Older MySQL versions were famously lenient about this — they'd run the query anyway and pick an arbitrary (essentially unpredictable) row's value for the non-grouped column. Modern MySQL (5.7.5+) enables ONLY_FULL_GROUP_BY in the default SQL mode, which brings its behavior in line with the ANSI rule — but the setting can be turned off, and plenty of production servers still run without it. SQL Server has never had a lenient mode at all: it has always enforced the strict rule that every non-aggregated column in the select list must appear in the GROUP BY clause, unconditionally.

⚠️ Gotcha

A query that "worked" against a MySQL server with ONLY_FULL_GROUP_BY disabled (or against an older MySQL version where the setting didn't exist yet) will not just behave differently on SQL Server — it will flatly refuse to compile. There's no equivalent lenient mode to fall back on; SQL Server rejects the query outright rather than silently picking an arbitrary value. If you're porting queries wholesale, this is one of the more common compile-time surprises — the fix is always the same: add every non-aggregated select-list column to the GROUP BY clause, or wrap it in an aggregate like MAX() if you genuinely don't care which value comes back.

Date/time function names

MySQL (what you know)

SELECT NOW();
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATEDIFF(end_date, start_date);

SQL Server (the new equivalent)

SELECT GETDATE();
SELECT DATEADD(DAY, 7, GETDATE());
SELECT DATEDIFF(DAY, StartDate, EndDate);

📋 Key differences

NOW() becomes GETDATE() (SQL Server also has SYSDATETIME() for higher precision, and GETUTCDATE() for the UTC equivalent of MySQL's UTC_TIMESTAMP()).
DATE_ADD(date, INTERVAL number unit) becomes DATEADD(unit, number, date) — note the argument order flips, and the single INTERVAL expression splits back into two separate arguments (unit first, then number).
SQL Server's DATEDIFF(unit, a, b) takes an explicit unit and computes b - a; MySQL's DATEDIFF(a, b) always returns whole days (a - b) with no unit argument — both the argument order and the meaning of the result are reversed, so this one is worth double-checking every time rather than trusting muscle memory.

🔧 Try It Yourself

On a SQL Server instance, run a GROUP BY query you'd normally write more loosely in MySQL — a column in the select list that isn't aggregated and isn't in the GROUP BY clause — and confirm it fails to compile rather than silently picking an arbitrary value. Then write one query using GETDATE() and DATEADD(DAY, 1, ...), translating from a MySQL query you'd normally write with NOW() and DATE_ADD(..., INTERVAL 1 DAY).

🙋 There Are No Dumb Questions

Q: Since SQL Server's default collation is case-insensitive, does that mean I never have to think about casing again?
A: Mostly, but not entirely. String comparisons and object name lookups are case-insensitive under the default collation, but the actual bytes you stored are preserved exactly as inserted — SELECT Name FROM Users still returns whatever casing was originally saved, it's just that WHERE Name = 'ada' will also match a row stored as 'Ada'. And if your application later needs to be deployed against a database using a case-sensitive collation (for compatibility with another system, for instance), any code that quietly relied on case-insensitive matching will start failing — so it's still worth being deliberate about casing in new code, even though the default collation is forgiving.

✅ Quick Check

A query with SELECT CustomerId, OrderDate, COUNT(*) FROM Orders GROUP BY CustomerId; ran fine on the team's old MySQL server but fails to compile after porting to SQL Server. What's the most likely explanation?

That's the last lesson — time for the recap, where all five lessons come together into one quick-reference summary.

← Back to