CREATE TABLE - Table Definition (DDL) - Oracle to SQL Server Migration

CREATE TABLE statement allows you to define a table in Oracle and SQL Server.

There are differences in data types, storage clauses between Oracle and Microsoft SQL Server.

Table Definition (DDL) Conversion Details

Oracle and SQL Server CREATE TABLE statements may use different syntax and require conversion.

Missed Data Type for Foreign Key Column

In Oracle, you can specify the column name without the data type for a column that is a foreign key. The data type of this column is inherited from the data type of the referenced column in the parent table:

Oracle:

   -- Create a parent table
   CREATE TABLE states
   (
      abbr CHAR(2) PRIMARY KEY,
      name VARCHAR2(90)
   );
 
   -- Create a child table
   CREATE TABLE cities
   (
      name VARCHAR2(90),
      state REFERENCES states
   );
 
   DESCRIBE cities
   -- NAME                           VARCHAR2(90)
   -- STATE                          CHAR(2)

You can see that Oracle automatically assigned CHAR(2) to state column.

SQL Server does not support this feature and requires specifying the data type explicitly:

SQL Server:

   -- Create a parent table
   CREATE TABLE states
   (
      abbr CHAR(2) PRIMARY KEY,
      name VARCHAR(90)
   );
 
   -- Create a child table
   CREATE TABLE cities
   (
      name VARCHAR(90),
      state REFERENCES states
   );
   -- Msg 173, Level 15, State 1, Line 4
   -- The definition for column 'state' must include a data type.
 
   CREATE TABLE cities
   (
      name VARCHAR(90),
      state CHAR(2) REFERENCES states
   );
   -- Command(s) completed successfully.