Oracle → SQL Server / Azure SQL Engine Internals

Companion to the Oracle → Azure SQL conceptual mapping. Where that page maps business outcomes, this one maps the engine internals — bytes on the page, latch classes, lock managers, recovery, encryption — as a reference for DBAs running Oracle and Azure SQL side by side, or modernizing one to the other.

🎯 Exam Focus

Content is verified against Microsoft Learn (learn.microsoft.com/sql/...?view=sql-server-ver17) and the Oracle Database Concepts Guide (12c / 19c / 21c). Behaviors flagged with ⚠️ differ enough between engines that they regularly cause migration surprises. Items flagged 🆕 require SQL Server 2025 / Azure SQL Database current generation.

---

1. Latches, Spinlocks, Mutexes — Synchronization Primitives

Both engines layer three levels of intra-process synchronization on top of OS sync. Vocabularies overlap; implementations differ.

Concern	Oracle	SQL Server / Azure SQL
Cheapest, busy-wait only	Mutex (since 10gR2 for library-cache cursors) — atomic CAS, no sleep, no post/wait	Spinlock — busy-wait on a kernel-mode lock-bit; backoff after threshold
Short-term structural protection	Latch — spin then sleep; held microseconds	Latch — short-term physical protection of in-memory structures (pages, allocation maps, log buffer); modes KP / SH / UP / EX / DT
Logical, transactional, long-term	Enqueue (TX, TM, UL, ST, HW, SQ…) — full lock manager with queue & modes	Lock — full lock manager: KEY / RID / PAGE / EXTENT / OBJECT / METADATA / DATABASE; modes IS, S, U, IX, SIX, X, BU, RangeS-S, …

⚠️ Naming trap. What Oracle calls a latch and what SQL Server calls a latch are the same concept. What SQL Server calls a spinlock maps best to Oracle's mutex. What both call lock / enqueue is the heavyweight, transaction-scoped object.

SQL Server latch types

Latch class	Protects	Wait stat
BUF (buffer latch)	Individual page in buffer pool	PAGELATCH_* (in-memory) / PAGEIOLATCH_* (page being read from disk)
Non-buffer latches (~150 classes)	Internal structures (allocation bitmaps, log buffer, tempdb metadata, version store)	LATCH_*
Super-latch	Auto-promotion of read-mostly buffer latches to a per-CPU sub-latch	Reduces PAGELATCH_SH contention

Oracle event ↔ SQL Server wait crosswalk

Oracle event	SQL Server equivalent	Cause
cache buffers chains latch	PAGELATCH_SH / PAGELATCH_EX on a hot page	Many sessions hashing to the same buffer header
latch: shared pool	LATCH_EX on ACCESS_METHODS_HOBT_* / spinlock SOS_CACHESTORE	Plan cache / metadata contention
library cache: mutex X	Spinlock on plan cache hash bucket	Hot reused plan
enq: TX – row lock contention	LCK_M_X on KEY / RID, or LCK_M_S_XACT_* (🆕 SQL 2025 with Optimized Locking)	Writer waiting for writer
enq: TX – allocate ITL entry	(no analog)	Block ran out of ITL slots
enq: TM – contention	LCK_M_Sch-S / LCK_M_Sch-M (schema lock)	DDL vs DML
log file sync	WRITELOG	Commit waiting for log flush
db file sequential read	PAGEIOLATCH_SH	Single-block read from data file
db file scattered read	PAGEIOLATCH_SH (multi-page)	Read-ahead / extent read
gc cr block 2-way (RAC)	HADR_SYNC_COMMIT (closest in AG)	Cross-node block transfer

Diagnostics

-- SQL Server / Azure SQL
SELECT * FROM sys.dm_os_spinlock_stats ORDER BY spins DESC;
SELECT * FROM sys.dm_os_latch_stats    ORDER BY waiting_requests_count DESC;
SELECT * FROM sys.dm_os_wait_stats
  WHERE wait_type LIKE 'PAGELATCH%' OR wait_type LIKE 'LATCH%' OR wait_type LIKE 'LCK%'
  ORDER BY wait_time_ms DESC;

-- Oracle
SELECT name, gets, misses, sleeps FROM v$latch ORDER BY misses DESC;
SELECT * FROM v$mutex_sleep_history;
SELECT event, total_waits, time_waited FROM v$system_event ORDER BY time_waited DESC;

---

2. Block / Page Format — Bytes on the Floor

The single highest-value internal to learn for cross-engine debugging.

Layout side by side

Region	Oracle block (default 8 KB)	SQL Server page (8 KB, fixed)
Top	Cache layer (~20 B): block address, block type, format, SCN	Page header (96 B): m_pageId, m_type, m_objId, m_indexId, m_freeCnt, m_lsn, m_slotCnt, m_freeData, m_xactReserved, m_ghostRecCnt, m_tornBits
Then	Transaction layer = ITL slots (24 B each, INITRANS preallocated, growable to MAXTRANS or until PCTFREE blocks growth)	(no equivalent — locks live in the lock manager, not on the page; until 🆕 2025 TID stamps)
Then	Table directory (clustered tables only)	n/a
Then	Row directory (one entry per row, 2 B each, points down to row data)	(no equivalent here)
Body	Free space, then row data growing downward from end	Row data growing forward from byte 96
Bottom	(free space meets row data)	Slot array: 2 bytes per row, each = byte offset to row start, grows backward

Row format

Field	Oracle row	SQL Server row
Row header	3 B: flag byte (cluster key, deleted, head/chain), lock byte = ITL slot index, column count	4 B status bits (TagA + TagB) + 2 B Length of fixed portion
Body	Per-column 1 B length (or 3 B if ≥ 250) + bytes; trailing-NULL columns omitted	Fixed-length cols; then 2 B Total column count + null bitmap; then 2 B Variable-column count + 2-B-per-column variable offset array, then variable column data

Row size & overflow

Limit	Oracle	SQL Server
Max row in block	block size − overhead (~7.9 KB for 8 KB block)	8060 B in-row, hard limit
Long row strategy	Row chaining when row > block; row migration when UPDATE no longer fits	ROW_OVERFLOW_DATA allocation unit — variable cols (varchar, nvarchar, varbinary, sql_variant, CLR UDT) move out; 24-B in-row pointer to overflow page
LOB out-of-row	LOB locator (~20 B in-row) → LOB segment	LOB_DATA allocation unit — 16-B pointer in-row → LOB tree (varchar(max), nvarchar(max), varbinary(max), xml, json)

What changes with 🆕 SQL Server 2025 Optimized Locking

Change	Effect
Each row gets a TID stamp (transaction ID of last modifier), persisted with the row when ADR is on	Plays the role of Oracle's lock byte + ITL entry — but with no fixed slot count and no per-block contention
Row & page locks are still acquired during the modify, then released immediately (before commit)	Only the single XACT/TID lock survives until commit
With RCSI: predicates evaluated on the versioned image before locking (Lock-After-Qualification)	Closes Oracle's "consistent-read for predicate eval" gap
Skip Index Locks (SIL) for INSERT on heaps and UPDATE on clustered/nonclustered indexes & heaps	Closer to Hekaton's lock-free behavior than to traditional row-locking

⚠️ Oracle's per-block ITL exhaustion (enq: TX – allocate ITL entry) has no SQL Server analog — neither classic nor 2025. SQL Server lock memory is global; TID stamping is per-row, not per-block.

📖 Deep-dive: SQL Server 2025 Optimized Locking — prerequisites, T-SQL to verify, limitations, and the full Oracle DBA translation table.

Inspecting a real page / block

-- SQL Server / Azure SQL
SELECT * FROM sys.dm_db_page_info(DB_ID(), 1, <page_id>, 'DETAILED');
SELECT * FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'DETAILED');
DBCC PAGE('ContosoDB', 1, 100, 3) WITH TABLERESULTS;   -- requires TF 3604

-- Oracle
SELECT dbms_rowid.rowid_block_number(rowid), dbms_rowid.rowid_relative_fno(rowid)
  FROM orders WHERE rownum = 1;
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 100;
SELECT * FROM v$transaction;

---

3. Columnstore vs Oracle In-Memory Column Store

Both stores are columnar with compression and zone elimination — but SQL Server columnstore IS the on-disk format, while Oracle In-Memory is a memory-only mirror of the row store.

Concern	Oracle In-Memory Column Store (12c+)	SQL Server Columnstore (2014+)
Where columnar data lives	In-Memory Area of the SGA (RAM only)	On disk + memory; clustered CS replaces the heap, nonclustered CS sits beside a rowstore
Authoritative copy	Row store on disk	Columnstore on disk is the authoritative copy (clustered CS)
Unit of compression	IMCU = In-Memory Compression Unit, ~ 0.5–1 M rows	Rowgroup = up to 2²⁰ (≈ 1 048 576) rows; one column segment per column per rowgroup
Encodings	Dictionary, RLE, bit-packing, common-prefix	Dictionary (global + local) + RLE + bit-packing + value encoding
Zone elimination	Storage Index (auto, in-memory min/max + bitmap per IMCU)	Min/max per segment in sys.column_store_segments
Concurrent DML	TX modifies row store + an IM journal; IMCU repopulated periodically	Inserts < 102 400 rows go to the delta store (a regular B-tree); tuple mover compresses them later
Updates	UPDATE → row store, IM journal marks rows stale	UPDATE = mark old row deleted in rowgroup (deleted bitmap) + insert new in delta store
Bulk insert	Direct-path insert, populates IM in background	Bulk insert ≥ 102 400 rows compresses directly to a closed rowgroup
License	Database In-Memory option (extra cost, EE)	Free in all editions since SQL Server 2016 SP1

-- SQL Server / Azure SQL
SELECT * FROM sys.column_store_segments;       -- min/max per segment
SELECT * FROM sys.column_store_row_groups;     -- state, deleted_rows, total_rows
SELECT * FROM sys.column_store_dictionaries;

-- Oracle
SELECT * FROM v$im_segments;
SELECT * FROM v$inmemory_area;
SELECT segment_name, populate_status, bytes, bytes_not_populated FROM v$im_user_segments;

⚠️ Don't treat them as the same thing. Oracle In-Memory is an acceleration layer — drop it and the database still works. SQL Server columnstore is the table (clustered) — you can't "drop columnstore and keep the data" without rebuilding the table.

---

4. Hekaton (Memory-Optimized Tables) vs Oracle Database In-Memory

Both are "in memory" but solve different problems: Hekaton is OLTP (single-row, lock-free writes); Oracle In-Memory is OLAP (scan-heavy queries on transactional data).

Concern	Oracle In-Memory (12c+)	SQL Server / Azure SQL Hekaton (2014+)
Workload target	OLAP scan acceleration	OLTP write throughput, hot tables
Format in memory	Columnar (in IM Area)	Row (memory-optimized data structure, multi-version)
On-disk persistence	Row store remains the source of truth	Checkpoint files (data + delta) + tail of transaction log
Concurrency	Same as row store (locks/MVCC via UNDO)	Optimistic MVCC, no locks, no latches, no spinlocks; rows have multiple versions chained in the same data structure
Conflict detection	UNDO + SCN	Validation phase at commit; on conflict → ROLLBACK with error 41302 / 41305 / 41325
Indexes	B-tree (heap), bitmap, IMCU min/max	Hash (point lookup, fixed bucket count) + range / Bw-tree (ordered scan)
Stored procs	PL/SQL (interpreted or native compiled)	Natively compiled procs → C → DLL loaded into sqlservr.exe; WITH NATIVE_COMPILATION, SCHEMABINDING
Durability	Always durable	DURABILITY = SCHEMA_AND_DATA (default) or SCHEMA_ONLY (data lost on restart, useful for staging)
Logging	Same redo / UNDO	Less log volume — single log record per transaction (vs per-row)
tempdb impact	Minimal	Zero — version store is in the same data structure, not in tempdb

In Hekaton each row carries BeginTs and EndTs timestamps. A logical row can have many physical versions chained in the same hash/range index buckets. Garbage collection is cooperative (worker threads sweep dead versions whose EndTs < oldest_active_tx_ts).

⚠️ Always Encrypted with secure enclaves is not supported on memory-optimized columns; AE is also disallowed inside a natively compiled module that references such a table.

---

5. Query Store ↔ AWR / ASH / SPM

Concern	Oracle (AWR + ASH + SPM)	SQL Server / Azure SQL Query Store (2016+)
Captures	System-wide stats snapshots + sampled session activity	Per-database queries, plans, runtime stats, wait stats, query hints
Sampling	MMNL samples v$session every 1 s into v$active_session_history (in-memory ring)	Continuous capture (mode: ALL, AUTO, NONE, CUSTOM)
Persistence	MMON snapshots every 60 min (default) into AWR; ~10 % of ASH samples flushed to dba_hist_active_sess_history	Aggregated runtime + wait stats stored in the user DB (asynchronously flushed)
Retention	AWR: 8 days default	Configurable: STALE_QUERY_THRESHOLD_DAYS + MAX_STORAGE_SIZE_MB, sliding window
Plan stability	SPM Baselines — engine prefers accepted plans	Forced plans (sp_query_store_force_plan) — engine forces the chosen plan
Auto-tuning	SQL Tuning Advisor + auto SPM evolution (12c)	Automatic Tuning (AUTOMATIC_TUNING.FORCE_LAST_GOOD_PLAN = ON) — auto-forces last good plan on regression
Wait analysis	dba_hist_system_event, v$active_session_history.event	sys.query_store_wait_stats — per-query wait categories
License	Diagnostics + Tuning Pack (extra cost, EE)	Free in all editions; on by default in new DBs since 2022 and in Azure SQL DB

SELECT * FROM sys.query_store_query;
SELECT * FROM sys.query_store_plan;
SELECT * FROM sys.query_store_runtime_stats;
SELECT * FROM sys.query_store_wait_stats;       -- per-plan wait category breakdown
SELECT * FROM sys.query_store_plan_feedback;    -- LAQ / CE / DOP feedback (2022+)
EXEC sp_query_store_force_plan @query_id = ..., @plan_id = ...;

⚠️ Licensing trap during migration: AWR / ASH / SPM require Diagnostics + Tuning Packs on Oracle EE. Query Store is free even on Express and is on by default in Azure SQL Database.

---

6. Resource Governor ↔ Resource Manager

Concern	Oracle Database Resource Manager	SQL Server Resource Governor (2008+, EE only on box product)
Hierarchy	Resource Plan → Plan Directives → Consumer Groups	Workload Group (per-request limits) → Resource Pool (CPU / memory / IO at pool level)
Classifier	dbms_resource_manager.set_consumer_group_mapping (by user, service, module, program, OS user, …)	Classifier UDF in master; runs at session login, returns workload group name
CPU control	mgmt_p1..p8 percentages, max_utilization_limit, parallel_target_percentage	MIN_CPU_PERCENT, MAX_CPU_PERCENT, CAP_CPU_PERCENT (hard cap, 2012+)
Memory	parallel_server_limit, undo_pool	MIN_MEMORY_PERCENT, MAX_MEMORY_PERCENT, REQUEST_MAX_MEMORY_GRANT_PERCENT
Parallel	parallel_degree_limit_p1..8, parallel_target_percentage	MAX_DOP (per workload group)
I/O	Exadata IORM, mgmt_p1..p8 for IO	MIN_IOPS_PER_VOLUME / MAX_IOPS_PER_VOLUME (2014+)
Runaway	switch_io_megabytes, switch_estimated_execution_time, switch_io_logical	REQUEST_MAX_CPU_TIME_SEC, REQUEST_MEMORY_GRANT_TIMEOUT_SEC
Multi-tenant	CDB-aware (PDB-level plans)	Not container-aware on box product — Azure SQL elastic pools provide the equivalent for Azure SQL Database

CREATE RESOURCE POOL HighPriPool
  WITH (MIN_CPU_PERCENT=20, MAX_CPU_PERCENT=80, CAP_CPU_PERCENT=80, MAX_DOP=4);
CREATE WORKLOAD GROUP HighPriGroup
  USING HighPriPool WITH (IMPORTANCE = HIGH, REQUEST_MAX_MEMORY_GRANT_PERCENT=50);

CREATE FUNCTION dbo.ClassifyMe() RETURNS sysname WITH SCHEMABINDING AS
BEGIN
  RETURN CASE WHEN SUSER_SNAME() LIKE 'app_%' THEN N'HighPriGroup' ELSE N'default' END;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifyMe);
ALTER RESOURCE GOVERNOR RECONFIGURE;

⚠️ Resource Governor is not available in Azure SQL Database. Use service tier sizing + elastic pools + read-scale-out replicas instead.

---

7. Service Broker ↔ Oracle Advanced Queuing (AQ)

Both ship transactional, queued messaging inside the database engine — there is no MQ-broker process; the queue is just a table.

Concern	Oracle AQ (since 8i)	SQL Server Service Broker (since 2005)
Storage	Queue table (dbms_aqadm.create_queue_table); queue is a logical view over it	Hidden queue table created by CREATE QUEUE; accessible via RECEIVE only
Message types	Object type / RAW / XMLType / JMS payloads	CREATE MESSAGE TYPE (XML, well-formed XML, binary, empty) bound to schemas
Conversation pattern	Single-consumer or multi-consumer queue; dbms_aqadm.add_subscriber	Dialog conversation (full duplex, ordered, exactly-once, exactly-in-order) bound by contracts
Send / receive	dbms_aq.enqueue, dbms_aq.dequeue (browse / locked / remove modes)	BEGIN DIALOG CONVERSATION, SEND ON CONVERSATION, RECEIVE TOP(N) FROM Queue
Cross-DB / cross-instance	Propagation scheduler + database links	Routes — CREATE ROUTE … WITH ADDRESS = 'TCP://host:4022'; dedicated TCP endpoint
Activation	Notification + callback procedure	Internal activation — queue can auto-start a stored proc when messages arrive (MAX_QUEUE_READERS)
Poison message	Retry count + exception queue	After 5 rollbacks → queue is automatically DISABLED
Status	Production, used by Streams (legacy), OGG	Production, used internally by Database Mail, Query Notifications, Event Notifications

⚠️ Service Broker has no native JMS interop and is not available in Azure SQL Database (it remains in SQL MI and SQL Server). For cross-platform messaging from Azure SQL DB, prefer Azure Service Bus.

---

8. Partitioning — Why You Often Need to Bridge

Strategy	Oracle	SQL Server / Azure SQL
RANGE	Yes	Yes (the only native option) — CREATE PARTITION FUNCTION pf (int) AS RANGE RIGHT FOR VALUES (...)
LIST	Yes	❌ — workaround: filtered indexes + partitioned views, or computed-column → range
HASH	Yes	❌ — workaround: persisted computed column HASHBYTES('SHA1', key) % N, then RANGE on it
Composite (RANGE-HASH, RANGE-LIST, INTERVAL-HASH)	Yes	❌ — only single-column RANGE; multi-key requires a composite computed column
Reference partitioning (child inherits parent's partition key)	Yes	❌
Interval partitioning (auto-create partitions on insert)	Yes	❌ — workaround: SQL Agent / Elastic Job runs ALTER PARTITION FUNCTION … SPLIT RANGE (...) ahead of time
Partition switching (metadata-only data move)	Yes (exchange partition)	Yes — ALTER TABLE … SWITCH PARTITION
Partition-wise join	Yes (engine-aware)	Yes for aligned partitioned tables
Truncate partition	ALTER TABLE … TRUNCATE PARTITION	TRUNCATE TABLE … WITH (PARTITIONS (n,m,o)) (2016+)
Per-partition statistics	Yes	Incremental statistics (2014+)
Max partitions	1 048 575 (12.2+)	15 000 (2008 SP2+) / 1 000 000 (2017+ via trace flag)

Sliding-window pattern (Azure SQL / SQL Server)

-- Pre-create the next partition before data arrives
ALTER PARTITION SCHEME ps_monthly NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_monthly() SPLIT RANGE ('2026-06-01');

-- Switch out the oldest partition (instant, metadata only)
ALTER TABLE Sales SWITCH PARTITION 1 TO Sales_Archive PARTITION 1;
ALTER PARTITION FUNCTION pf_monthly() MERGE RANGE ('2025-06-01');

⚠️ Watch for partition alignment: in SQL Server, the clustered + nonclustered indexes must use the same partition scheme to enable partition-wise operations. Oracle does this automatically with local indexes.

---

9. Backup & Restore — RMAN ↔ T-SQL BACKUP / Azure-Managed

Concern	Oracle RMAN	SQL Server / SQL on Azure VM	Azure SQL Database / MI
Catalog	Recovery catalog DB (preferred) or controlfile	msdb.dbo.backupset / backupfile / backupmediafamily / backupmediaset	Platform-managed; visible via PITR and LTR
Granularity	Database, tablespace, datafile, archivelog, controlfile, SPFILE	Database, filegroup, file, partial, log	Database only
Full	BACKUP DATABASE	BACKUP DATABASE … TO DISK = '…'	Automatic weekly
Incremental	BACKUP INCREMENTAL LEVEL 0/1, differential vs cumulative	Differential (single level only)	Automatic 12–24 h
Block-change tracking	ALTER DATABASE ENABLE BLOCK CHANGE TRACKING (file bct.dbf)	Built in — DCM page tracks changed extents (always on, no config)	Same
Log backup / archivelog	BACKUP ARCHIVELOG ALL DELETE INPUT	BACKUP LOG db TO DISK='…' (FULL / BULK_LOGGED only)	Automatic 5–10 min
Compression	BACKUP … AS COMPRESSED BACKUPSET	WITH COMPRESSION (since 2008 R2 Std)	Automatic
Encryption	CONFIGURE ENCRYPTION FOR DATABASE ON (TDE wallet or password)	WITH ENCRYPTION (ALGORITHM=…, SERVER CERTIFICATE=…) (2014+)	Automatic; CMK via Key Vault
Verify	RESTORE VALIDATE, BACKUP … VALIDATE, VALIDATE DATABASE	BACKUP … WITH CHECKSUM, RESTORE VERIFYONLY, DBCC CHECKDB	Platform-validated; run DBCC CHECKDB on demand
Point-in-time	RESTORE … UNTIL TIME 'sysdate-1/24'	RESTORE LOG … WITH STOPAT = 'yyyy-mm-dd hh:mm:ss'	PITR to any second within retention (7–35 d), via portal or Restore-AzSqlDatabase
Tail-of-log	Implicit in RECOVER flow	Tail-log backup required: BACKUP LOG db TO DISK='…' WITH NO_TRUNCATE, CONTINUE_AFTER_ERROR	n/a — managed
Direct to cloud	RMAN to OSB / S3 / OCI Object Storage	BACKUP TO URL (Azure Blob Storage), since 2012 SP1 CU2	Backups are already in geo-redundant storage
Long-term archive	RMAN to tape / cloud	Custom + cold-tier blob	LTR up to 10 years

Recovery model — the SQL Server gotcha

Recovery model	Log behavior	Point-in-time?
SIMPLE	Log auto-truncated at each checkpoint	❌ — equivalent to Oracle NOARCHIVELOG
BULK_LOGGED	Bulk ops minimally logged	⚠️ Yes between log backups, not to a moment inside a bulk op
FULL	Every change logged, log truncated only after log backup	✅ — equivalent to Oracle ARCHIVELOG

⚠️ Switching from FULL → SIMPLE breaks the log chain. After switching back, take a fresh full backup before relying on log restores. Azure SQL Database is always in FULL and you cannot change it.

---

10. Always Encrypted vs Oracle TDE Column Encryption

This is where SQL Server has a stronger out-of-the-box threat model than Oracle.

Threat model

Threat	Oracle TDE column	SQL Server TDE (full DB)	SQL Server / Azure SQL Always Encrypted
Disk theft	✅	✅	✅ (ciphertext only on disk)
Backup theft	✅	✅	✅
Memory dump on server	❌ Plaintext in SGA	❌ Plaintext in buffer pool	✅ Plaintext never on server
Malicious DBA	❌ Can SELECT plaintext	❌ Can SELECT plaintext	✅ DBA sees ciphertext only
Compromised app server	❌	❌	❌ (driver decrypts)

Key hierarchy

Component	Oracle TDE	SQL Server TDE	SQL Server / Azure SQL Always Encrypted
Master key	Wallet (file or HSM)	Service Master Key → Database Master Key → Server Certificate (or Azure Key Vault for CMK)	Column Master Key (CMK) — cert in Windows store / Azure Key Vault / HSM, lives on the client
Data key	Column encryption key in DB metadata, encrypted by master	Database Encryption Key (DEK) in DB boot record, encrypted by Server Cert / Key Vault key	Column Encryption Key (CEK) in DB metadata, encrypted by CMK; server cannot decrypt
Decryption location	Server side, on read	Server side, on page read into buffer pool	Client driver (.NET, JDBC, ODBC, Python pyodbc, OLE DB)

Encryption modes (Always Encrypted)

Mode	Behavior	Trade-off
Deterministic	Same plaintext → same ciphertext	Equality predicates, joins, GROUP BY work; vulnerable to inference attacks on low-cardinality columns
Randomized	Same plaintext → different ciphertext	Maximum security; no server-side ops beyond IS NULL / equality on the same encrypted value within a single query (driver substitutes)

🆕 Always Encrypted with Secure Enclaves (SQL Server 2019+, Azure SQL DB)

Capability	Without enclave	With enclave (VBS / Intel SGX)
Equality	Deterministic only	Deterministic + Randomized (in enclave)
Range / LIKE	❌	✅ — predicate pushed into enclave, evaluated on plaintext inside attested memory
In-place encryption	❌ — required client-side encrypt + reload	✅ — enclave does it server-side
Indexes on randomized cols	❌	✅ (via enclave)

Wire format

Engine	Protocol
Oracle TDE	TDE columns sent decrypted over the network (use Native Network Encryption / TLS for transit)
SQL Server TDE	Same — TDE = at-rest only
Always Encrypted	Ciphertext stays ciphertext on the wire; driver decrypts in app process memory

⚠️ Always Encrypted is not TDE. TDE protects files at rest; Always Encrypted protects data from the DBA and the server. They compose — use both.

---

Where SQL Server 2025 / Azure SQL Closes Historical Oracle Gaps

Historical Oracle advantage	SQL Server 2025 / Azure SQL closing it with
Non-blocking readers, non-blocking writers (UNDO + ITL)	🆕 Optimized Locking (TID stamping + LAQ + SIL); requires ADR + RCSI
Persistent versioned reads	Persistent Version Store (PVS) inside ADR — versions in user DB, not tempdb
O(1) long-tx rollback	Accelerated Database Recovery (ADR) (sLog + PVS + logical revert)
Mature plan baseline (SPM)	Query Store + forced plans + Automatic Tuning
Partitioning richness (LIST / HASH / composite)	Still a gap — only RANGE native (workarounds in §8)
RAC scale-out	Still a gap — Always On AG / Business Critical is HA, not active-active scale-out
In-Memory column store option	Columnstore is free in all editions since SQL 2016 SP1 and in all Azure SQL service tiers

---

Source documents

• Microsoft Learn — Pages and Extents Architecture Guide
• Microsoft Learn — SQL Server Transaction Log Architecture and Management Guide
• Microsoft Learn — Optimized Locking
• Microsoft Learn — In-Memory OLTP — Introduction to Memory-Optimized Tables
• Microsoft Learn — Accelerated Database Recovery concepts
• Microsoft Learn — Persistent Version Store
• Microsoft Learn — Transaction Locking and Row Versioning Guide
• Microsoft Learn — Always Encrypted (incl. with Secure Enclaves)
• Microsoft Learn — Query Store, Resource Governor, Service Broker, Partitioned Tables and Indexes
• Microsoft Learn — Backup and Restore (SQL Server) and Automated Backups (Azure SQL Database)
• Oracle Database Concepts Guide (12c / 19c / 21c) — "Data Concurrency and Consistency", "Data Blocks, Extents, and Segments", "Database In-Memory", "Backup and Recovery Concepts", "Advanced Queuing"

🎯 Exam Focus

The full local working copy of this content (with extra T-SQL snippets and the V$↔DMV cheat sheet) is kept at Work/Learning/Oracle-to-SQLServer-Engine-Map.md for personal reference.