Skip to content
Tutorials Center
My learning

Topics

Lesson 96 of 125 ยท Database Systems

SQL Server and Oracle Essentials

Learn the key differences in Microsoft SQL Server and Oracle, from TOP, IDENTITY, square brackets and MERGE to FETCH FIRST, sequences, DUAL and empty strings.

Microsoft SQL Server and Oracle Database are the two big commercial databases. You'll find them inside banks, insurers, hospitals, airlines, government agencies and most large enterprises, often running systems that are decades old and still growing. SQL Server's dialect is called T-SQL (Transact-SQL); Oracle's procedural extension is PL/SQL, and its plain SQL follows the standard closely, with some famous quirks.

The good news: SELECT, WHERE, joins, GROUP BY, subqueries, CTEs and window functions all work the way you learned them. This lesson focuses on the parts that don't: limiting rows, generating ids, quoting, MERGE, sequences, DUAL, and how each system treats NULL and empty text. The read-only code targets SQL Server 2022 and 2025, and Oracle Database 23ai (re-branded Oracle AI Database 26ai in late 2025; the SQL here is unchanged). Each idea comes with a runnable SQLite equivalent.

Getting a server to practice on

Both vendors offer free editions for learning. SQL Server has the Developer edition (full features, not for production) and the small Express edition; it runs on Windows, Linux and in Docker. Oracle offers Oracle Database Free, also available as a container image. Typical clients:

  • SQL Server: SQL Server Management Studio (SSMS), the sqlcmd command-line tool, or the MSSQL extension for VS Code.
  • Oracle: SQL Developer (desktop or VS Code extension), the sqlcl command line, or the classic SQL*Plus.
Terminal
# SQL Server: run one query with sqlcmd
sqlcmd -S localhost -U app -d shop -Q "SELECT TOP (3) name FROM dbo.products;"

# Oracle: connect with SQLcl to a pluggable database called FREEPDB1
sql app@localhost:1521/FREEPDB1

One SQL Server detail confuses newcomers: GO is not a T-SQL statement. It is a batch separator understood by SSMS and sqlcmd, which split your script at each GO and send the pieces one by one.

Limiting rows: TOP and FETCH FIRST

SQL Server has no LIMIT. Its traditional syntax is TOP, placed right after SELECT:

SQL
-- SQL Server
SELECT TOP (5) name, price
FROM dbo.products
ORDER BY price DESC;

-- Keep extra rows that tie with the last one
SELECT TOP (7) WITH TIES name, stock
FROM dbo.products
ORDER BY stock DESC;

Both SQL Server (2012+) and Oracle (12c+) also support the standard syntax, which is the one to prefer because it handles paging too:

SQL
-- SQL Server and Oracle (standard SQL)
SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Oracle: first rows only, including ties
SELECT name, stock
FROM products
ORDER BY stock DESC
FETCH FIRST 7 ROWS WITH TIES;

In SQL Server, OFFSET ... FETCH requires an ORDER BY. Older Oracle code uses the ROWNUM pseudo-column instead, with a well-known trap: WHERE ROWNUM <= 5 ORDER BY price DESC numbers the rows before sorting, so it returns five arbitrary rows, sorted. That's why legacy Oracle queries wrap the sorted query in a subquery first.

SQLite uses LIMIT. There is no WITH TIES, but a RANK() window gives the same result, because tied rows share a rank:

SQL TOP, LIMIT and WITH TIES ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query cuts off after seven rows, even though three products share a stock of 57. The tie-aware version returns nine rows, keeping the Trail Headlamp, Folding Camp Chair and Dot Grid Notebook together. See LIMIT, OFFSET and Pagination and ROW_NUMBER, RANK and DENSE_RANK for more.

Quoting and naming

SQL Server quotes identifiers with square brackets: [order], [first name]. Double quotes also work for identifiers as long as the QUOTED_IDENTIFIER setting is on, which is the default. Objects live in schemas, and the default schema is dbo, so you'll often see dbo.products or even three-part names like shop.dbo.products.

Oracle quotes identifiers with double quotes, as the standard says, but adds a twist: unquoted names are stored in UPPERCASE. CREATE TABLE products creates PRODUCTS, and SELECT * FROM products finds it. If someone creates a table as "Products", you must write "Products" with the exact case forever after. Another Oracle rule: table aliases can't use AS.

SQL
-- Oracle: column aliases may use AS, table aliases may not
SELECT p.name AS product_name
FROM products p;          -- "FROM products AS p" is an error in Oracle

SQLite accepts square brackets for compatibility, which makes T-SQL snippets easier to try:

SQL Square brackets in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Server's default collation is case-insensitive (for example SQL_Latin1_General_CP1_CI_AS), like MySQL's. Oracle compares text case-sensitively by default, like SQLite and PostgreSQL.

Data types

Need SQL Server Oracle SQLite
Whole numbers INT, BIGINT NUMBER(10) INTEGER
Exact decimals DECIMAL(10, 2) NUMBER(10, 2) INTEGER cents or REAL
Unicode text NVARCHAR(100), NVARCHAR(MAX) VARCHAR2(100 CHAR), CLOB TEXT
True/false BIT (0/1) BOOLEAN (23ai+) INTEGER 0/1
Date and time DATE, DATETIME2 DATE (includes time!), TIMESTAMP TEXT in ISO format
Unique ids UNIQUEIDENTIFIER RAW(16) TEXT or BLOB
JSON JSON (2025+), else NVARCHAR JSON (21c+) TEXT

Two things stand out. In SQL Server, VARCHAR traditionally holds single-byte text, so use NVARCHAR for names and other international text (or a UTF-8 collation, available since 2019). In Oracle, a DATE stores a time of day too, so WHERE ordered_at = DATE '2025-03-01' misses orders placed after midnight. Also note that SQL Server has no Boolean expressions in a SELECT list: SELECT price > 100 is an error, so you write IIF(price > 100, 1, 0) or a CASE.

Identity columns and getting the new id

SQL Server's auto-numbering is IDENTITY(seed, increment). The OUTPUT clause returns values from the affected rows, much like RETURNING:

SQL
-- SQL Server
CREATE TABLE dbo.categories (
  id INT IDENTITY(1, 1) PRIMARY KEY,
  name NVARCHAR(100) NOT NULL UNIQUE
);

INSERT INTO dbo.categories (name)
OUTPUT inserted.id, inserted.name
VALUES (N'Garden'), (N'Pets');

SELECT SCOPE_IDENTITY();   -- last id generated in this scope

The N prefix marks a Unicode string literal. Oracle (12c+) uses the standard identity syntax, and its RETURNING ... INTO clause stores values in variables inside PL/SQL:

SQL
-- Oracle
CREATE TABLE categories (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL UNIQUE
);

In SQLite, RETURNING covers both OUTPUT and RETURNING ... INTO, and it works on UPDATE and DELETE too, just like SQL Server's OUTPUT deleted.*:

SQL OUTPUT becomes RETURNING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The new categories get ids 7 and 8, and the four Audio products come back with their raised prices, such as 86.9 for the Wireless Earbuds. One difference: SQL Server's OUTPUT can show the old value (deleted.price) and the new value (inserted.price) side by side, while SQLite's RETURNING shows only the new row.

Sequences

A sequence is a database object that hands out numbers, independent of any table. Oracle relied on sequences for decades before identity columns existed, and SQL Server (2012+) and PostgreSQL support them too:

SQL
-- Oracle
CREATE SEQUENCE invoice_seq START WITH 1000 INCREMENT BY 1;

INSERT INTO invoices (id, customer_id)
VALUES (invoice_seq.NEXTVAL, 42);

SELECT invoice_seq.CURRVAL FROM dual;   -- the value this session just took

-- SQL Server
CREATE SEQUENCE dbo.invoice_seq START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR dbo.invoice_seq;

Sequences are useful when several tables share one numbering, or when you need the number before inserting the row. They never roll back: if a transaction fails after taking a number, that number is gone, so expect gaps. SQLite has no sequences, but a tiny counter table with UPDATE ... RETURNING imitates one:

SQL A sequence imitated in SQLite

Press Run (or Ctrl + Enter) to see the result here.

The two calls return 1000 and 1001. Because SQLite allows only one writer at a time, two connections can't receive the same number.

MERGE

MERGE is the SQL standard's statement for "synchronize a target table with a source". It matches rows on a condition, then updates, inserts or deletes them. SQL Server, Oracle and PostgreSQL 15+ support it:

SQL
-- SQL Server: sync prices from a staging table (note the final semicolon)
MERGE dbo.products WITH (HOLDLOCK) AS target
USING dbo.price_updates AS source
  ON target.sku = source.sku
WHEN MATCHED THEN
  UPDATE SET target.price = source.price
WHEN NOT MATCHED BY TARGET THEN
  INSERT (sku, name, price) VALUES (source.sku, source.name, source.price)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

-- Oracle: the ON condition needs parentheses
MERGE INTO products t
USING price_updates s
  ON (t.sku = s.sku)
WHEN MATCHED THEN
  UPDATE SET t.price = s.price
WHEN NOT MATCHED THEN
  INSERT (sku, name, price) VALUES (s.sku, s.name, s.price);

In SQL Server, a MERGE statement must end with a semicolon, and under concurrent load it can still hit duplicate-key errors unless you add the HOLDLOCK hint shown above. Many teams prefer separate UPDATE and INSERT statements inside a transaction for that reason. WHEN NOT MATCHED BY SOURCE (delete rows missing from the source) is a SQL Server extension.

SQLite has no MERGE, but an upsert plus a DELETE does the same job:

SQL MERGE as upsert plus delete

Press Run (or Ctrl + Enter) to see the result here.

A1 drops to 27, D4 is added, and B2 is deleted because the source no longer lists it. The WHERE true is a SQLite parsing detail: without it, ON CONFLICT right after SELECT ... FROM would be read as the start of a join condition. Upserts with ON CONFLICT explains the details.

DUAL, NULL and the empty string

Oracle traditionally requires a FROM clause in every SELECT, so it provides a one-row dummy table called DUAL for calculations. Oracle 23ai finally makes FROM optional, but you'll see DUAL in nearly all existing code. SQL Server never needed it.

SQL
-- Oracle (any version)
SELECT SYSDATE, 2 + 3 FROM dual;

-- Oracle 23ai, SQL Server, SQLite, PostgreSQL, MySQL
SELECT 2 + 3;

Oracle has a much bigger quirk: an empty string is NULL. '' and NULL are the same value, so WHERE middle_name = '' never matches anything, and LENGTH('') is NULL rather than 0. On the other hand, Oracle's || treats NULL as empty text, so 'a' || NULL is 'a'. In SQL Server, 'a' + NULL is NULL, while CONCAT('a', NULL) gives 'a'. SQLite follows the standard: '' is a real, empty string, and 'a' || NULL is NULL. Its concat() function (SQLite 3.44+) skips NULLs like SQL Server's CONCAT:

SQL Empty strings and NULL in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQLite reports 0 (not NULL), 0, NULL and ab. In Oracle, the first two answers would be "true" and NULL. When you migrate data from Oracle, empty strings turn into NULLs; when you migrate to Oracle, empty strings silently become NULL and can break NOT NULL columns. Read Working with NULL for the full story.

Function cheat sheet

Most daily differences are just function names:

Task SQL Server Oracle SQLite
Current date and time SYSDATETIME(), GETDATE() SYSTIMESTAMP, SYSDATE datetime('now')
Add 30 days DATEADD(day, 30, d) d + 30 date(d, '+30 days')
Days between DATEDIFF(day, a, b) b - a julianday(b) - julianday(a)
Format a date FORMAT(d, 'yyyy-MM') TO_CHAR(d, 'YYYY-MM') strftime('%Y-%m', d)
Default for NULL ISNULL(x, 0), COALESCE NVL(x, 0), COALESCE ifnull(x, 0), coalesce
String length LEN(s) (ignores trailing spaces) LENGTH(s) length(s)
Concatenate +, CONCAT(), || (2025+) ||, CONCAT(a, b) ||, concat()
Join strings in a group STRING_AGG(x, ', ') WITHIN GROUP (ORDER BY x) LISTAGG(x, ', ') WITHIN GROUP (ORDER BY x) group_concat(x, ', ' ORDER BY x)
Pattern match LIKE, REGEXP_LIKE (2025+) LIKE, REGEXP_LIKE LIKE, GLOB

Here is one row of that table in action, with the group concatenation that every report needs:

SQL STRING_AGG and LISTAGG in SQLite ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Design lists Farah, Kofi, Umar and Wen, and Finance lists Farah, Goran, Mateo, Omar and Uma.

Recent additions worth knowing

Both systems are still evolving quickly, and recent versions remove many old annoyances:

  • SQL Server 2022: GREATEST/LEAST, GENERATE_SERIES, IS [NOT] DISTINCT FROM, the WINDOW clause, DATE_BUCKET and JSON_OBJECT/JSON_ARRAY.
  • SQL Server 2025: a native json type, REGEXP_LIKE and related regular-expression functions, the || concatenation operator, and a vector type for AI similarity search (vector indexes are still a preview feature that you must switch on explicitly).
  • Oracle 23ai: SELECT without FROM, a real BOOLEAN type, IF [NOT] EXISTS for CREATE and DROP, GROUP BY on column aliases, multi-row INSERT ... VALUES (...), (...), JSON-relational duality views, SQL property-graph queries and a VECTOR type.

T-SQL and PL/SQL also include full procedural languages, with variables (DECLARE @total INT in T-SQL), loops and error handling. That's the subject of the next lesson.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which query returns the five most expensive products in SQL Server?

    SQL Server has no LIMIT; it uses TOP (n) after SELECT, or the standard OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY.
  2. 2. In Oracle, what does SELECT COUNT(*) FROM people WHERE nickname = ''; return?

    Oracle stores empty strings as NULL, and comparing with NULL gives unknown. You'd need WHERE nickname IS NULL.
  3. 3. In the SQLite demo, why does TOP (7) WITH TIES on stock return nine rows?

    Three products share a stock of 57 at the cutoff. WITH TIES, like RANK() <= 7, keeps all rows equal to the last one.
  4. 4. What is GO in a SQL Server script?

    The server never sees GO. Client tools split the script at each GO and send each batch separately.
  5. 5. Which SQLite feature replaces SQL Server's OUTPUT inserted.id?

    RETURNING returns columns from every inserted, updated or deleted row, just like OUTPUT does.

Summary

  • SQL Server limits rows with TOP (n); both SQL Server and Oracle support the standard OFFSET ... FETCH, including WITH TIES. Avoid ROWNUM with ORDER BY.
  • SQL Server quotes names with [brackets] and uses schemas like dbo; Oracle uppercases unquoted names and forbids AS before table aliases.
  • IDENTITY + OUTPUT (SQL Server) and identity columns + sequences (Oracle) generate ids; SQLite uses INTEGER PRIMARY KEY + RETURNING.
  • MERGE synchronizes tables in one statement; in SQLite use an upsert plus a DELETE.
  • DUAL is Oracle's one-row table; 23ai makes FROM optional.
  • In Oracle, '' is NULL; in SQL Server, 'a' + NULL is NULL. SQLite follows the standard.
  • Expect different function names for dates, NULL defaults and string aggregation.

Next: Stored Procedures and Functions, where you'll write procedural code in PL/pgSQL, MySQL and T-SQL, and see how SQLite apps do the same.