The CREATE TABLE syntax for Oracle and Microsoft® SQL Server™ is similar. Both Oracle and SQL Server support SQL-92 standard naming conventions for identifying database management system (DBMS) objects.
| Oracle | SQL Server |
|---|---|
| CREATE TABLE [schema.]table_name ( { {column_name data_type} [DEFAULT constant_expression] [ column_reference ] [ column constraint ] [...n] | table_reference | table_constraint } [,...n] ) [Oracle Specific Data Storage Parameters] |
CREATE TABLE [database_name.[owner]. | owner.] table_name ( { {column_name data_type} [ [DEFAULT constant_expression] | [IDENTITY [(seed, increment) [NOT FOR REPLICATION] ] ] [ROWGUIDCOL] [ column constraint ] [...n] | column_name AS computed_expression | table_constraint } [,...n] ) [ON {filegroup | DEFAULT} ] [TEXTIMAGE_ON {filegroup | DEFAULT }] |
While segments are familiar to Oracle administrators and are useful in some high-end configurations, they are not needed for most SQL Server installations. Using segments introduces administrative complexity and the possibility for error. In most cases, hardware-based RAID or Microsoft Windows NT® software-based RAID solutions are recommended for use with SQL Server.
Using RAID simplifies the table-definition and index-definition processes. The SQL Server table uses RAID to control its placement.
| Oracle | SQL Server |
|---|---|
| CREATE TABLE DEPT_ADMIN.DEPT ( DEPT VARCHAR2(4) NOT NULL, DNAME VARCHAR2(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY (DEPT) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE (DNAME) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED) ) PCTFREE 10 PCTUSED 40 TABLESPACE USER_DATA STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED FREELISTS 1) |
CREATE TABLE USER_DB.DEPT_ADMIN.DEPT ( DEPT VARCHAR(4) NOT NULL, DNAME VARCHAR(30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY CLUSTERED (DEPT), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE NONCLUSTERED (DNAME) ) |
When creating tables in SQL Server using SELECT statements, it is recommended that you use a SELECT...INTO statement.
| Oracle | SQL Server |
|---|---|
CREATE TABLE |
SELECT * INTO |
Note A SELECT...INTO statement does not work unless the database in which this is done has the select into/bulkcopy option set to true. Only members of the db_owner and sysadmin roles can set this option using SQL Server Enterprise Manager or the sp_dboption system stored procedure.
The sp_helpdb system stored procedure can be used to check the status of the database. If select into/bulkcopy is not set to true, you can still use a SELECT statement to copy into a temporary table:
select * into #student_backup from user_db.student_admin.student
As in Oracle, when a table is copied in SQL Server, the referential integrity definitions are not copied to the new table.
If you need to copy data into tables using a SELECT statement, consider creating the table first, and then using the INSERT INTO...SELECT statement to load the table. This method is compatible between Oracle and SQL Server, and does not require that any database option be set. This method is slower in SQL Server because each row is logged.
| Creating and Modifying a Table | CREATE TABLE |
| RAID | SELECT |
| Setting Database Options | sp_dboption |
| sp_helpdb | SELECT INTO |