Lesson 1 of 5

Syntax & Data Types

The good news first: both Oracle and SQL Server speak SQL, and 80% of a query looks identical in either. The friction shows up at the edges — statement terminators, the exact names of data types, and how each engine treats NULL and empty strings. Get those out of the way first and the rest of the transition is mostly vocabulary.

💡 The Bottom Line

Oracle is strict about semicolons ending PL/SQL statements; SQL Server's T-SQL is far more relaxed and often doesn't need them at all (though modern best practice adds them anyway). Oracle's VARCHAR2/NUMBER/DATE map to SQL Server's VARCHAR/DECIMAL/DATETIME2 — similar jobs, different names, slightly different edge-case behavior. And the two engines disagree, sharply, on what an empty string is.

Statement terminators

Oracle PL/SQL

SELECT last_name
FROM employees
WHERE department_id = 10;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Done');
END;
/

SQL Server T-SQL

SELECT last_name
FROM employees
WHERE department_id = 10;

PRINT 'Done';
GO

📋 Key differences

Oracle requires a semicolon after every PL/SQL statement; a trailing / on its own line tells SQL*Plus/SQL Developer to execute the preceding block.
SQL Server uses GO as a batch separator for tools like SSMS — it's not part of T-SQL itself, and semicolons are statement terminators that are often optional (though required before some statements like WITH common table expressions).
DBMS_OUTPUT.PUT_LINE becomes PRINT for simple debug output.

Data types: same job, different names

Oracle

CREATE TABLE employees (
  employee_id   NUMBER(10),
  first_name    VARCHAR2(50),
  hire_date     DATE,
  salary        NUMBER(10,2)
);

SQL Server

CREATE TABLE employees (
  employee_id   INT,
  first_name    VARCHAR(50),
  hire_date     DATETIME2,
  salary        DECIMAL(10,2)
);

📋 Key differences

VARCHAR2(n) → VARCHAR(n). SQL Server's plain VARCHAR is fine here; there's no VARCHAR2 at all in T-SQL.
NUMBER(p,s) → DECIMAL(p,s) (or NUMERIC, an alias). For whole numbers, Oracle developers usually just used NUMBER with no scale — SQL Server has real integer types (INT, BIGINT, SMALLINT) that are cheaper to store and index than a decimal.
Oracle's DATE actually stores a time component down to the second — it behaves more like SQL Server's DATETIME2 than like a date-only type. SQL Server also has a true date-only type, DATE, with no time component at all.
For Unicode text, Oracle's NVARCHAR2 maps to SQL Server's NVARCHAR.

String concatenation

Oracle

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

SQL Server

SELECT first_name + ' ' + last_name AS full_name
FROM employees;
-- or: CONCAT(first_name, ' ', last_name)

Oracle's concatenation operator is ||. SQL Server overloads the plain + operator for strings instead — which is convenient until you concatenate a NULL (see the gotcha below), so many T-SQL developers prefer the explicit CONCAT() function, which sidesteps that trap entirely.

⚠️ Gotcha

In Oracle, 'Hello' || NULL evaluates to 'Hello' — Oracle's || treats NULL as an empty string for concatenation purposes. In SQL Server, 'Hello' + NULL evaluates to NULL — the entire expression collapses to NULL the moment one operand is NULL. If you're porting an Oracle report that concatenates optional (nullable) columns, use CONCAT() or wrap the nullable column in ISNULL(col, ''), or every row with a missing middle name will show a blank full name instead of "first last".

NULL handling functions

Oracle

SELECT NVL(commission_pct, 0) AS commission
FROM employees;

SELECT NVL2(commission_pct, 'Yes', 'No')
FROM employees;

SQL Server

SELECT ISNULL(commission_pct, 0) AS commission
FROM employees;

SELECT IIF(commission_pct IS NOT NULL, 'Yes', 'No')
FROM employees;

📋 Key differences

NVL(expr, replacement) maps most directly to ISNULL(expr, replacement) — both return the replacement only when the first argument is NULL.
The ANSI-standard COALESCE(a, b, c, ...) works identically in both Oracle and SQL Server (returns the first non-null argument) — prefer it when you want code that's portable across engines, since ISNULL is T-SQL-only.
Oracle's NVL2 (three-way: value, if-not-null, if-null) has no single T-SQL equivalent — use IIF(condition, true_val, false_val) or a CASE expression instead.

🔧 Try It Yourself

Take a CREATE TABLE statement from an Oracle schema you know and rewrite it for SQL Server: swap VARCHAR2 for VARCHAR, pick between DATE and DATETIME2 based on whether you actually need a time component, and replace any NUMBER primary key with INT. Then rewrite one query that uses || and NVL, converting both to their T-SQL equivalents.

🙋 There Are No Dumb Questions

Q: Can I just use COALESCE everywhere instead of learning ISNULL?
A: Yes, and it's arguably the better habit — COALESCE is ANSI SQL and works in both Oracle and SQL Server (and Postgres, MySQL, etc.), while ISNULL only exists in T-SQL and NVL only exists in Oracle. The one wrinkle: COALESCE's result type is determined by data type precedence across all its arguments, which can occasionally surprise you, whereas ISNULL's result type always matches its first argument. For simple two-argument cases the difference rarely matters.

✅ Quick Check

You're porting an Oracle report that builds a display name with first_name || ' ' || middle_name || ' ' || last_name, where middle_name is often NULL. You rewrite it in T-SQL using plain +. What happens for rows where middle_name is NULL?

Next up: once your tables and types are in place, the next friction point is how you query them — pagination, the mysterious DUAL table, and a handful of renamed string functions.

← Back to