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.
Oracle and SQL Server CREATE TABLE statements may use different syntax and require conversion.
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.
Oracle 11g R2 SQL Language Reference
Microsoft SQL Server 2012 - Books Online
Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012