SQL Server and Oracle Create Table Comparison

By: Pablo Echeverria   |   Updated: 2022-03-09   |   Comments (2)   |   Related: > Database Design

Problem

If you work with databases, you may already know how to create a table, define columns, specify data types, add a Primary Key, add Foreign Keys and constraints; if not, there are guides to help you understand it easily in Oracle and in SQL Server, and the differences in data types can be found in this article Comparing SQL Server and Oracle datatypes. Apart from standard tables and columns, what other things are possible for tables in SQL Server and Oracle?

Solution

Below you can find different things you can do with tables and columns when defining a new table. In this tutorial, we look at what is available in SQL Server on Windows, Oracle on Windows and both. This will help you get the most of your existing infrastructure reducing development effort, or let you choose one or the other depending on your requirements. Note that some of these items can be combined together, but others are mutually exclusive and in some cases a property can't be changed after creation. For further reading refer to this documentation for Oracle and for SQL Server.

Similar Craete Table Statement Attributes for SQL Server and Oracle

Similarities between SQL Server and Oracle are described below, except their indexing properties which can be specified independently.

Invisible/Hidden Column

To not show a column until you explicitly include it for security reasons or to disallow users from modifying critical parts of the system and without having to create and maintain another object like a view.

• In SQL Server this is only available in Temporal Tables specifying HIDDEN at the column level.
• In Oracle this is available by specifying INVISIBLE at the column level for all table types except EXTERNAL, CLUSTER or TEMPORARY tables.

Encrypted Column

This is more efficient than encrypting the whole database or the whole tablespace (Oracle) since only the sensitive data is encrypted.

• In SQL Server this is achieved with ENCRYPTED WITH, can be DETERMINISTIC (always produce same value, allows index and joins) or RANDOMIZED, and the only algorithm available is AEAD_AES_256_CBC_HMAC_SHA_256.
• In Oracle this is achieved with ENCRYPT USING, the available algorithms are AES256, AS192, AES128, 3DES168, ARIA128, ARIA192, ARIA256, GOST256 and SEED128, it allows you to specify a different password for each column, it allows you to choose an integrity algorithm SHA-1 (which decreases performance and can't be compressed) or NOMAC, and allows you to add a SALT (default if not specified) or NO SALT (always produce same value, allows index and joins); note this is being deprecated in favor of tablespace encryption.

Virtual/Computed Column

To define a column that is the result of an operation (calculated) instead of a value.

• In SQL Server this is achieved with <column_name> AS <computed_column_expression> syntax.
• In Oracle this is achieved with <column_name> <datatype> GENERATED ALWAYS AS <computed_column_expression> VIRTUAL, and you can specify an edition of a PL/SQL function in which it is evaluated or in which it is unusable.

Identity Column

To generate key values automatically in concurrent transactions, note this does not guarantee uniqueness, and does not guarantee consecutive values (when caching is enabled).

• In SQL Server this is achieved with <column_name> IDENTITY (<first_value>, <increment>), and you can specify NOT FOR REPLICATION to not increment it during replication.
• In Oracle this is achieved with GENERATED ( ALWAYS | BY DEFAULT ON NULL ) AS IDENTITY START WITH ( <integer> | LIMIT VALUE ) INCREMENT BY <integer> MAXVALUE ( <integer> | NOMAXVALUE ) MINVALUE ( <integer> | NOMINVALUE ) ( CYCLE | NOCYCLE ) ( CACHE <integer> | NOCACHE ) ( ORDER | NOORDER ), some of these options are the same as for sequences.

ROWGUIDCOL or ROWID Column

Used to differentiate records in a faster way without using the primary key or the unique constrained column(s), but this doesn't guarantee uniqueness or immutability so you should not use them as the primary key.

• In SQL Server this is used in replication and FileStream by specifying ROWGUIDCOL at the column level.
• In Oracle this is a pseudocolumn named ROWID already present in all tables but invisible, also note there is a ROWID data type that can be used.

Large Object Column

To hold large amounts of data.

• In SQL Server this is achieved with FILESTREAM (stored in the file system, requires a special filegroup), FileTable (available externally as if it was in the file system, allows full-text and semantic search, doesn't allow memory-mapped files), and Remote Blob Store (file stored separately from the database, only the reference is stored, allows access control and encryption); note there are also big/unlimited-size field types but can store a max 2 GB, those can be stored in a separate filegroup with the clause TEXTIMAGE_ON and are: TEXT, IMAGE, XML, VARCHAR(MAX), VARBINARY(MAX), GEOMETRY and GEOGRAPHY.
• In Oracle this is called LOB, can hold up to 128 TB of data, can be partitioned, and is achieved with any of these data types: BLOB (binary), CLOB (character), NCLOB (national character set), BFILE (external file, read only), VARRAY STORE AS LOB (or when the size is greater than 4000), XMLType, ORDAudio, ORDDoc, ORDImage, ORDVideo; note the LONG type is deprecated, the storage can be specified as BASICFILE (backward compatible) or SECUREFILE (high-performance, allows deduplication, compression, encryption), and it allows specifying caching and logging.

Flashback Archive / Versioned Row

To enable historical tracking of the rows in a separate table to run flashback queries at a specified time in the past.

• In SQL Server this is achieved with SYSTEM_VERSIONING = ON HISTORY_TABLE = <history_table>, DATA_CONSISTENCY_CHECK = ( ON | OFF ).
• In Oracle this is achieved with FLASHBACK ARCHIVE <archive> RETENTION <integer> YEAR | MONTH | DAY.

Temporal Validity / Archival / Data Retention Row

Allows you to define a period for which each row is considered valid.

• In SQL Server this is achieved with the column clauses GENERATED ALWAYS AS ROW START, GENERATED ALWAYS AS ROW END, and PERIOD FOR, or you can perform cleanup of old or aged data based on policy with DATA_DELETION = ON FILTER_COLUMN = <column_name> RETENTION_PERIOD = <period_of_time>.
• In Oracle this is achieved with the table clause PERIOD FOR and two columns for start and end time, or with the table clause ROW ARCHIVAL which adds a hidden column to indicate if the row is archived.

External Table

This enables you to access data in external sources as if it were in a table in the database.

• In SQL Server this is achieved with CREATE EXTERNAL TABLE <name> (<column_definition>) WITH (LOCATION=<location>, DATA_SOURCE=<data_source>, FILE_FORMAT=<file_format>, <reject_options>) where LOCATION is a folder when querying Hadoop or Windows Azure Storage, DATA_SOURCE must exist as described here, FILE_FORMAT must exist for Hadoop and Windows Azure Storage as described here, and the reject options are the ones for Hadoop or Windows Azure Storage described here; notice it allows connecting to other RDBMS through native protocols or with ODBC.
• In Oracle this is achieved with CREATE TABLE <name> (<column_definition>) ORGANIZATION EXTERNAL (TYPE <type> DEFAULT DIRECTORY <default_directory> ACCESS PARAMETERS (<access_parameters>|USING CLOB <subquery>) LOCATION (<location>)) REJECT LIMIT <integer>|UNLIMITED where TYPE can be ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS or ORACLE_HIVE, CLOB is used to derive the access parameters and their values, and LOCATION can be a local directory but doesn't need to be. Note in Oracle they are compatible with other specifications: in memory, partitioned, parallel, encryption, etc.  And in addition to supporting external data residing in operating file systems and Big Data sources and formats such as HDFS and Hive, Oracle supports external data residing in object stores via the DBMS_CLOUD package.

Compressed Table

To use less storage and reduce I/O.

• In SQL Server this is achieved with DATA_COMPRESSION = ROW for few duplicate values or PAGE for repeated values by column + patterns on the entire page, and you can do this differently for each partition.
• In Oracle this is achieved with ROW STORE COMPRESS BASIC (during direct path insert) or ADVANCED (during all DML operations).

Compressed Columnstore Table

To occupy less memory for in-memory tables where data is not frequently updated.

• In SQL Server this is achieved with DATA_COMPRESSION = ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE (more compression but takes more time), you can select on which partitions, and you can specify a delay (to use all available space on closed delta rowgroups).
• In Oracle this is achieved with COLUMN STORE COMPRESS FOR QUERY or ARCHIVE (more compression, more CPU, more time) LOW or HIGH (more compression) ROW LEVEL LOCKING (for performance), and you can select on which partitions.

In-memory Table

To perform all table operations in memory, and the table can persist on disk if needed.

• In SQL Server this is achieved with MEMORY_OPTIMIZED=ON DURABILITY=SCHEMA_ONLY or SCHEMA_AND_DATA.
• In Oracle this is achieved with INMEMORY, you can differentiate the data population priority among different tables with PRIORITY ( NONE | LOW | MEDIUM | HIGH | CRITICAL ), you can distribute data across RAC instances with DISTRIBUTE ( AUTO | BY ROWID RANGE | BY PARTITION | BY SUBPARTITION ), and you can duplicate data across RAC instances with DUPLICATE (on one additional instance) or DUPLICATE ALL (on all instances).

Temporary Table

This is a temporary table only visible to the current session.

• In SQL Server you only prefix the table name with a number sign #<table_name>.
• In Oracle you specify CREATE PRIVATE TEMPORARY TABLE, and you can specify if the table is dropped at the end of the transaction with ON COMMIT DROP DEFINITION or at the end of the session with ON COMMIT PRESERVE DEFINITION.

Global Temporary Table

This is a temporary table visible to all sessions.

• In SQL Server you only prefix the table name with a double number sign ##<table_name>, the data can be accessed from any session, and it is dropped when both the session that created the table ends and the last active T-SQL statement referencing it ends.
• In Oracle you specify CREATE GLOBAL TEMPORARY TABLE but only the table definition is shared (the data is private to any session), and you can specify if the rows are dropped at the end of the transaction with ON COMMIT DELETE ROWS or if they are dropped at the end of the session with ON COMMIT PRESERVE ROWS.

Partitioned Table

To divide a table into segments, making it easier to manage each segment independently and improving performance.

• In SQL Server this is achieved with ON <partition_scheme> ( <partition_column> ), which requires a partition scheme and a partition function.
• In Oracle this is achieved with PARTITION(SET) BY ( RANGE | (CONSISTENT) HASH | LIST | REFERENCE | SYSTEM ) SUBPARTITION BY ( RANGE | HASH | LIST ) ( INTERNAL | EXTERNAL ) OVERFLOW, each type has different characteristics and usage.

Spatial table

To manage geometry and geography point locations, and perform operations on them.

• In SQL Server this is achieved with the data types of GEOMETRY and GEOGRAPHY.
• In Oracle this is achieved with the data type SDO_GEOMETRY.

Oracle Only

Tablespace

This is the data storage location at the logical level, which allows specifying the same characteristics to several tables and/or partitions, reducing contention and giving more flexibility over: limiting space usage, putting files offline, making files read-only, performing backup or recovery only on some files, allocating files across devices to improve performance, etc. The official documentation can be found here.

Tablespace Group

Enables multiple default temporary tablespaces to be used in different sessions at the same time while reducing contention. This helps avoid the problem caused when one temporary tablespace doesn't have enough space to hold the results of a sort.

Scoped Column

To use less storage by storing pointers or reference to an existing row instead of its value. This is achieved with SCOPE IS, and you can include WITH ROWID to improve performance of queries.

Zone Mapped Columns

A zone is a set of contiguous data blocks on disk, where it is tracked the min and max value of the column(s) in the zone, to reduce I/O and improve performance by doing zone map elimination. This is achieved using WITH MATERIALIZED ZONEMAP.

Attribute Clustered Columns

To cluster data from multiple tables in close physical proximity to reduce I/O and improve performance. This is achieved with CLUSTERING <table1> JOIN <table2> ON <join> BY LINEAR | INTERLEAVED ORDER <column> YES | NO ON LOAD YES | NO ON DATA MOVEMENT WITH | WITHOUT MATERIALIZED ZONEMAP <zone>, where INTERLEAVED is used for multidimensional multicolumn z-ordering, ON LOAD to do it during serial or parallel direct-path insert or merge, ON DATA MOVEMENT to do it during redefinition or partition maintenance.

Substitutable Column Type

To indicate object-oriented columns or attributes in the same hierarchy are substitutable for each other (polymorphism). This is achieved with ELEMENT IS OF TYPE ( ONLY <type> ) (NOT) SUBSTITUTABLE AT ALL LEVELS.

Sharded Table

To break large tables into data chunks or partitions that reside on separate servers, distributing resource load and improving query throughput and response times. This is only available in Azure SQL Server, but in Oracle you specify SHARDED and you can decide between RANGE or LIST sharding, DUPLICATE data in all shards, decide which tables belong together through a PARENT reducing multishard joins, and group tables from different tablespaces using TABLESPACE SET.

Blockchain Table

To create tamper-proof tables. This is only available in Azure SQL Server through LEDGER tables, but in Oracle you specify BLOCKCHAIN and you can specify IMMUTABLE to make it unchained, insert-only.

Application Common Table

To share data between the parent database and the contained databases. You specify SHARING and you can decide what to share: METADATA, DATA, or EXTENDED DATA (each PDB can have its own data apart from the CDB data).

Memory Optimized Table

Different than in-memory tables, this only stores the primary key as a hash index in memory (memoptimize pool) to not perform I/O and avoid resource contention. This is achieved with MEMOPTIMIZE FOR READ | WRITE, where READ is for fast lookup and WRITE is for fast ingest.

In-memory Compressed Table

To optimize memory and reduce execution time for in memory tables. This is achieved with MEMCOMPRESS FOR DML (low compression) | QUERY (best performance) | CAPACITY (best compression) LOW | HIGH.

Information Lifecycle Management (ILM) Table

To automate aging data compression, movement of data between storage tiers, and modify inmemory properties. In SQL Server you can only archive cold data to Azure (table stretch) with REMOTE_DATA_ARCHIVE=ON MIGRATION_STATE=OUTBOUND|PAUSED. In Oracle the compression is achieved specifying it as for other table types and including ( SEGMENT | GROUP | ROW ) AFTER <period_of_time> OF ( NO ACCESS | NO MODIFICATION | CREATION ), the tiering is achieved specifying TIER TO <tablespace> (READ ONLY) ( SEGMENT | GROUP ) AFTER <period_of_time> OF ( NO ACCESS | NO MODIFICATION | CREATION ), and the inmemory is achieved specifying ( SET | MODIFY | NO ) INMEMORY <inmemory_properties> AFTER <period_of_time> OF ( NO ACCESS | NO MODIFICATION | CREATION ).

Clustered Tables

To store together related rows from multiple tables which are not updated frequently neither full scanned, reducing I/O, time, and storage. This is achieved with CLUSTER <cluster_name> ( column(s) ) SORT.

Cache Table

To indicate how to store blocks in the buffer cache, except on direct reads or parallel table scans. This is achieved with CACHE (frequent access for small lookup tables), NOCACHE (infrequent access, for LOB indicates it is not cached) or CACHE READS (only for LOB, caches on read but not on write).

Read Only Table/ Partition / Subpartition

This is achieved with READ ONLY at the table/partition/subpartition level.

Parallel Table

To define independently the parallel creation of the table and the default degree of parallelism for queries and the DML INSERT, UPDATE, DELETE, and MERGE on it. This is achieved with PARALLEL, which selects a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

Row-level Dependency Tracking Table

To have each row have a 6-byte system change number (SCN) greater than or equal than the commit time of the last transaction that modified the row, enhancing parallel propagation in replication environments, but disabling compression. This is achieved with ROWDEPENDENCIES.

Row Movement table

To allow rows to move during compression or partition maintenance but disables static ROWIDs. This is achieved with ENABLE ROW MOVEMENT.

Logical Replication Table

To enable supplemental logging which is automatically captured by GoldenGate for replication. This is achieved with ENABLE LOGICAL REPLICATION ( ALL KEYS | ALLOW NOVALIDATE KEYS ).

Object Table

Object-oriented table based on an object type and not on a collection of columns. This is achieved with CREATE TABLE OF <object_type> ( <object_properties> ), where the columns correspond to the attributes of <object_type>, and each row is an object with a unique system generated object identifier OBJECT_ID.

XML Table

This is achieved with CREATE TABLE OF XMLTYPE ( <object_properties> ) ( BINARY XML | CLOB | OBJECT RELATIONAL ) XMLSCHEMA <schema> ELEMENT <element>, where CLOB is not recommended and OBJECT RELATIONAL allows you to define indexes and enhance query performance.

Nested Table

To link the subordinate data items to the base table using an object ID (pointer). This is achieved with CREATE TABLE <name> ( <columns> ) NESTED TABLE ( <nested_item> | COLUMN_VALUE ) ( LOCAL | GLOBAL ) STORE AS <storage_table> RETURN AS ( LOCATOR | VALUE ), where LOCAL equipartitions the nested table with the base table, storage_table is where the rows will reside (can't be queried directly), VALUE returns a copy of the table and LOCATOR returns a collection locator.

SQL Server Only

Sparse Column

To optimize storage for null values in a column. This is achieved with SPARSE at the column definition, and you can also define an XML COLUMN_SET FOR ALL_SPARSE_COLUMNS to improve performance when dealing with multiple sparse columns.

Masked Column

To obfuscate a column content. This is achieved with MASKED WITH ( FUNCTION = <mask_function> ), which can be default, email, partial or random.

Persisted Column

To store a computed column value in the table, allowing to create an index. This is achieved by specifying PERSISTED at the column level.

Graph Table

To manage collections of nodes (vertices) and edges (relationships) allowing pattern matching and navigation through queries. In SQL Server this is achieved with CREATE TABLE … AS ( NODE | EDGE ), the queries have a new operator MATCH, and there is a function for SHORTEST_PATH. In Oracle there is a separate installer for Graph Server and Graph Client, the database is created under a new schema, and the queries must be run through Java using the PGQL language.

Conclusion

As you can see, for basic and moderate-complex usage scenarios both SQL Server and Oracle support the same kind of tables, with Oracle having extra options on some of them. Oracle supports many other table types for very specific scenarios which aim to improve performance and reduce development effort and which SQL Server doesn't offer. Also, SQL Server has a few options that Oracle doesn't have which are very attractive commercially and which cover most customer requirements.

Next Steps

• Learn more about SQL Server and Oracle data type comparison also specifically for the VARCHAR type.
• Learn more about SQL Server and Oracle identity comparison and their gaps.
• Learn more about SQL Server creating tables with TSQL.

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips