Lesson 5 of 5

Common Gotchas

The last mile of this transition isn't new syntax — it's the handful of behavioral differences that don't show up as an error message, just as wrong results. These are the ones that cost the most debugging time, because the query runs fine; it just doesn't mean what you think it means.

💡 The Bottom Line

Oracle treats an empty string as NULL; SQL Server treats them as genuinely different values. Oracle string comparisons are case-sensitive by default; SQL Server's default collation is usually case-insensitive. Oracle auto-starts a transaction and expects an explicit COMMIT; SQL Server autocommits every statement unless you explicitly open a transaction. And a handful of everyday functions — starting with SYSDATE vs GETDATE() — just have different names.

Empty string vs NULL

Oracle

INSERT INTO customers (name, notes)
VALUES ('Ada', '');

SELECT * FROM customers
WHERE notes IS NULL;  -- matches the row above

SQL Server

INSERT INTO customers (name, notes)
VALUES ('Ada', '');

SELECT * FROM customers
WHERE notes IS NULL;  -- does NOT match

⚠️ Gotcha

Oracle silently converts an empty string literal ('') to NULL for VARCHAR2/CHAR columns — Oracle simply has no separate concept of "a zero-length string" at the storage level for these types. SQL Server has no such conversion: an empty string is a real, distinct value with length zero, and WHERE notes IS NULL will not match it. Code ported from Oracle that relies on "empty means null" (e.g. WHERE notes IS NULL OR notes IS NOT NULL reasoning) needs an explicit WHERE notes IS NULL OR notes = '' in SQL Server to behave the same way.

Case sensitivity

Oracle (default)

SELECT * FROM employees
WHERE last_name = 'smith';
-- no match if stored as 'Smith'

SQL Server (default collation)

SELECT * FROM employees
WHERE last_name = 'smith';
-- matches 'Smith' too

📋 Key differences

Oracle string comparisons are case-sensitive by default (unless you enable a case-insensitive session parameter or wrap both sides in UPPER()/LOWER()).
SQL Server's behavior depends entirely on the column/database collation — most default installations use a case-insensitive collation (e.g. SQL_Latin1_General_CP1_CI_AS, where CI means case-insensitive), so 'smith' = 'Smith' is often true out of the box.
This is configurable in both directions on both engines — the point isn't that one is "right," it's that you can't assume the Oracle behavior carries over. Always check the target database/column's collation rather than assuming case sensitivity either way.

Transactions and autocommit

Oracle

UPDATE employees SET salary = salary * 1.1
WHERE department_id = 10;
-- change is NOT permanent yet

COMMIT;
-- now it is

SQL Server

UPDATE employees SET salary = salary * 1.1
WHERE department_id = 10;
-- change IS already permanent

-- only needed if you opened one:
BEGIN TRAN; ... COMMIT;

⚠️ Gotcha

Oracle's client tools implicitly start a transaction with your first DML statement, and it stays open — uncommitted, and rollback-able — until you explicitly COMMIT or ROLLBACK (or disconnect, which triggers an automatic rollback in many clients). SQL Server's default is autocommit: every individual statement is its own implicit transaction, committed the instant it succeeds. If you're used to Oracle's habit of running a batch of changes and eyeballing them before a final COMMIT, that safety net is gone by default in SQL Server — you must explicitly wrap statements in BEGIN TRANSACTION ... COMMIT TRANSACTION (or ROLLBACK TRANSACTION) to get the same "review before making permanent" behavior.

Date/time function names

Oracle

SELECT SYSDATE FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT MONTHS_BETWEEN(d1, d2) FROM DUAL;
SELECT TRUNC(SYSDATE) FROM DUAL;

SQL Server

SELECT GETDATE();
SELECT DATEADD(MONTH, 3, GETDATE());
SELECT DATEDIFF(MONTH, d2, d1);
SELECT CAST(GETDATE() AS DATE);

📋 Key differences

SYSDATE (no parens, returns server date/time) → GETDATE() (function call, same job). For UTC, Oracle's SYSTIMESTAMP maps to SQL Server's SYSUTCDATETIME()/GETUTCDATE().
ADD_MONTHS(date, n) → DATEADD(MONTH, n, date) — note SQL Server's generic DATEADD(unit, number, date) handles days/months/years/hours all through one function by changing the first argument, where Oracle has more separate named functions.
MONTHS_BETWEEN(d1, d2) → DATEDIFF(MONTH, d2, d1) — mind the argument order swap (Oracle: later date first; DATEDIFF: earlier date first as the "start").
Truncating a date to remove its time component: Oracle's TRUNC(date) → SQL Server's CAST(date AS DATE) (or CONVERT), since SQL Server has a true date-only type to cast into.

🔧 Try It Yourself

Find an Oracle query in your own codebase that filters on WHERE some_column IS NULL for a text column that might contain empty strings. Run the SQL Server equivalent against a test table with an explicit empty-string row and confirm it does not come back — then fix the query with WHERE some_column IS NULL OR some_column = '' so the behavior matches what the Oracle version assumed.

🙋 There Are No Dumb Questions

Q: Is there a setting to make SQL Server behave like Oracle for all of this — treat empty strings as NULL, be case-sensitive, require explicit COMMIT?
A: Partially, and it's not recommended. Case sensitivity can be changed via collation at the database or column level. Requiring explicit transactions can be approximated with SET IMPLICIT_TRANSACTIONS ON, which stops autocommitting individual statements. But there's no supported setting that makes SQL Server treat '' as NULL — that behavior is specific to how Oracle stores character data, not a configurable session option. The better long-term approach is to write SQL Server code that matches SQL Server's actual semantics rather than trying to reproduce Oracle's exactly.

✅ Quick Check

A junior developer ports an Oracle batch script to SQL Server. It runs ten UPDATE statements in a row with no COMMIT/BEGIN TRAN anywhere, intending to review the results before committing, the way they always did in Oracle. What actually happens on SQL Server?

That's the guide — time for the recap.

← Back to