Comparing Oracle and SQL Server Code - Part 1

CREATE DATABASE [DB2]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DB2', FILENAME = N'C:\SQL\db2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ssis_log', FILENAME = N'C:\sql\db2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [db2] SET COMPATIBILITY_LEVEL = 120
GO

ALTER DATABASE [db2] SET RECOVERY FULL
GO

ALTER DATABASE [DB2] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

The example creates a data file named db2.mdf which stores the data. The log file is stored in the db2_log.ldf the maximum size for the data file is unlimited and the log file can have a maximum size of 2048 GB. The compatibility level is used for backward compatibility with earlier SQL Server versions. The recovery model is used to set the mode to recover information using the transaction log files. Finally, the filestream option is used to store unstructured data.

In Oracle, creating a database is a more complex process. In order to see the steps, refer to this link:

Creating a Database with the CREATE DATABASE Statement.

USE DB2
GO

exec sp_databases
GO

CREATE TABLE customer
(
id INT NOT NULL ,
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DT DATE NULL ,
LASTNAME VARCHAR(30) NULL ,
CITY VARCHAR(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
GO

In this example, the primary key is the id which does not accept nulls, the other columns accept null values. By default the city is NY.

For more information about creating tables and about SQL Server data types, refer to these links:

• Create tables
• SQL Server Data types

CREATE TABLE customer
(
id INT NOT NULL ,
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DATE DATE NULL ,
LASTNAME VARCHAR2(30) NULL ,
CITY VARCHAR2(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
/

As you can see, Oracle and SQL Server are very similar, but in some cases the data types can differ.

For more information about creating tables and about Oracle data types, refer to these links:

• Creating tables
• Oracle data types

insert into dbo.customer
values (5,'John','m','10-21-1980',NULL,default)
go

Default is used to insert the value by default defined when creating a table. You can assign null values to columns if they accept null values.  Check out the SQL Server INSERT Tutorial.

insert into customer values(5,'John','m',to_date('10/21/1980','mm/dd/yyyy'), NULL,default);

The main difference between SQL Server and Oracle is that in Oracle you have to use the function to_date and specify the date format to insert the information.

CREATE TABLE products_sold
(
product_id numeric(10) not null,
customer_id INT not null,
CONSTRAINT fk_pr
FOREIGN KEY (customer_id)
REFERENCES customer(id) )
GO

The syntax for foreign keys is the same in Oracle and SQL Server.  Learn more about Foreign Keys.

CREATE TABLE products_sold
(
product_id numeric(10) not null,
customer_id INT not null,
CONSTRAINT fk_pr
FOREIGN KEY (customer_id)
REFERENCES customer(id) );

An identity is used to creates auto-generated values for rows. In this case (IDENTITY(1,1)) the value starts in 1 and the incremental value is 1.

CREATE TABLE CUSTOMER2
(id_num int IDENTITY(1,1),
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DT DATE NULL ,
LASTNAME VARCHAR(30) NULL ,
CITY VARCHAR(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
GO

You can also use sequences in SQL Server like in Oracle. For more information, check out this resource on  sequence numbers.

In Oracle there was not an auto increment option like in SQL Server until Oracle 12c.

You can use sequences that are similar than in SQL Server (they existed before than the SQL Server sequences). The following example shows a sequence from 1 to 1000.

CREATE SEQUENCE myseq
MINVALUE 1
MAXVALUE 1000
START WITH 1
INCREMENT BY 1
CACHE 20;

In order to insert a sequence use this example:

insert into customer values(myseq.nextval)

In Oracle 12c, you can use the identity option:

CREATE TABLE product (id NUMBER GENERATED ALWAYS AS IDENTITY, prod_name VARCHAR2(30) );

select CONVERT (date, GETDATE())
go

Learn more about SQL Server Dates.

SELECT TO_CHAR (SYSDATE, 'MM-DD-YY') "NOW" FROM DUAL;

select FORMAT (getdate(), 'MM-dd-yyyy') as date
GO

For more information, refer to this link.

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY') "DATE" FROM DUAL;

For more information, refer to this link:

http://docs.oracle.com/cd/B19306_01/server.102/

b14200/functions172.htm

select var(id) from customer
go

select variance(id) from customer;

or

select var(id) from customer;

SELECT CHARINDEX( 'Gates', 'Bill Gates', 1);

This example shows the position of the word Gates in Bill Gates.

SELECT INSTR('Bill Gates', 'Gates', 1) FROM dual;

In Oracle you also have INSTR2 (UCS2 code points), INSTR4 (UCS2 code points), INTRB (bytes), INSTRC (UNICODE) functions.

SELECT ROUND(RAND()*6,0)
GO

select trunc(dbms_random.value(1,6)) from dual;

declare @var int=1
select @var
go

VARIABLE var NUMBER BEGIN :varl:=1; END; / PRINT var;

SELECT table_name FROM information_schema.tables
go

select table_name from user_tables;

SELECT * FROM information_schema.views
go

SELECT View_name from user_views;