Comparison – Understanding Tables Between Oracle and SQL Server

Oracle and SQL Server tables share familiar ideas, but their names do not always describe the same behavior. After my earlier discussion of instances, friends from the Oracle community asked for more comparisons. Tables are a useful place to start.

This comparison needs more than a list of matching names. Some concepts are close, some only overlap, and others solve different problems. The table below corrects several overly simple matches in the original post.

Compare the purpose, then the behavior

Three names that deserve extra care

Clustered does not mean the same thing everywhere. An Oracle table cluster is different from a SQL Server clustered index. An indexed view is different again. For an Oracle IOT, a SQL Server rowstore clustered index is a useful comparison, but primary-key requirements and implementation details still differ.

Global temporary tables have different visibility. Do not translate an Oracle global temporary table to a SQL Server ## table based on the word global. Decide who should see the rows, when they should disappear and how the application creates and reuses the object.

In-memory is not one feature. Oracle Database In-Memory and SQL Server In-Memory OLTP are not interchangeable names. SQL Server also has columnstore indexes. Choose the technology for the workload, and verify the version, edition and licensing requirements separately.

Inspect the tables you actually have

In SQL Server, this read-only query lists visible user tables in the current database and their base storage index. It is a starting inventory, not a complete description of partitioning or every secondary index.

SELECT s.name AS schema_name,
       t.name AS table_name,
       i.type_desc AS base_storage,
       t.is_memory_optimized
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
LEFT JOIN sys.indexes AS i
  ON i.object_id = t.object_id AND i.index_id IN (0, 1)
ORDER BY s.name, t.name;

In Oracle, the following catalog query describes relational tables owned by the current user. A NULL IOT_TYPE normally means the table is not an index-organized table. TEMPORARY and PARTITIONED show their respective flags; CLUSTER_NAME identifies a table cluster when present.

SELECT table_name,
       temporary,
       partitioned,
       iot_type,
       cluster_name
FROM user_tables
ORDER BY table_name;

Run each query in its own database product. Catalog visibility and table ownership affect what you see. The Oracle query is based on the documented catalog columns; it has not been executed in an Oracle instance for this refresh.

Use the comparison when planning a change

For a migration, start with the reason the original object exists. Check its constraints, indexes, transaction behavior, row visibility and maintenance needs. Then test the target design with representative data and queries. A matching term alone cannot establish equivalent behavior or performance.

Have you worked with a table design that was easy to misunderstand when moving between these products? Share the specific behavior in the comments.

A cross-platform comparison is not a name translation, it is a check of storage and behavior.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.