For this blog, I create an employee table using the Oracle’s GUI tool called “SQL Developer”.  I then exported that syntax to a file.With a few changes I added to Microsoft SQL Server (after adding the schema first), and modified it to include a primary key. My only reason to port this over was to do some more blogs and comparison of how certain features work in Oracle’s PL/SQL, and how they work in Microsoft’s T-SQL; so stay tuned for more comparisons in future blogs.

I then reformatted it to make it “skinny” so I could so a side-by-side compare and squeeze it in the frame below.  Identity columns were not introduced into Oracle until release 12c, and I was using 11g for this demo.  Unfortunately, all the data types had to be changed.  Microsoft uses square brackets around table and column names (just in case they conflict with reserved words), while Oracle uses double quotes.  When I defined the table in Oracle, I should have probably made the EMPID of type PLS_INTEGER instead of NUMBER.

Oracle also generated the insert statements for me. I had to correct the EMPID on “Barnie Rubble” from a 3 to 4, because with the primary key constraint, I couldn’t load the data into SQL. Had I put an index on Oracle with a unique constraint, that should have also solved the problem. Other than that issue, the “INSERT” statements were 100% compatible as they are using standard ANSI SQL.

Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (1,'Neal','Walters','TX');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (2,'John','Doe','OK');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Fred','Flinstone',null);
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Barnie','Rubble',null);

However, since I added an identity field, I had to turn identity_insert off/on while running the query.

set identity_insert NEAL.EMPLOYEE on
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (1,'Neal','Walters','TX');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (2,'John','Doe','OK');
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (3,'Fred','Flinstone',null);
Insert into NEAL.EMPLOYEE (EMPID,FIRSTNAME,LASTNAME,STATE) values (4,'Barnie','Rubble',null);
set identity_insert NEAL.EMPLOYEE off

Oracle Create Table

  CREATE TABLE "NEAL"."EMPLOYEE" 
   (  "EMPID" NUMBER, 
  "FIRSTNAME" VARCHAR2(20 BYTE), 
  "LASTNAME" VARCHAR2(20 BYTE), 
  "STATE" VARCHAR2(2 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 
  MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 
  MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 
  FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT 
  FLASH_CACHE DEFAULT 
  CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

Microsoft SQLServer Create Table

CREATE TABLE [Neal].[EMPLOYEE](
  [EMPID] [int] IDENTITY(1,1) NOT NULL, 
  [FIRSTNAME] [varchar](20) NULL,
  [LASTNAME] [varchar](20) NULL,
  [STATE] [varchar](2) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
  [EMPID] ASC
)WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
) ON [PRIMARY]

GO