ORACLEvsSQLSERVER

INTRODUCTION

Oracle

In june 1970, Dr E.R.Codd published a paper entitled A Relational Model of Large Shared Data Banks. This model, sponsored by IBM, then came to be accepted as the definitive model for RDBMS. The language developed by IBM to manipulate the data stored within Codd’s model was originally called Structured Query Language (SEQUEL) with the word English later being dropped in favor Structured Query Language(SQL). In 1979 a company called Relational Software, Inc. released the first commercially available implementation of SQL. Relational Software later came to be known as Oracle Corporation. Oracle Corporation is a company that produces the most widely used, Server based, Multi user RDBMS named Oracle.

SQL Server

SQL Server is a database developed by Microsoft Corporation competiting Oracle. The Microsoft SQL Server is an ease-to-use database platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.

Differences between Oracle & SQL Server

ORACLE	SQL SERVER
1. Developed by Oracle Corporation.	Developed by Microsoft Corporation.
2. Oracle can runs on many platforms such as Windows, all flavors of Unix from vendors such as IBM, Sun, Digital, HP, Sequent, etc . and VAX-VMS, as well as MVS	SQL is specific to Windows Platform only. (A major limitation for it to be an enterprise solution.)
3. Oracle includes IFS (Internet File System), Java integration	It is more of a pure database.
4. Oracle has less Cost/Performance and overall Performance compared to SQL Server	SQL is better in Cost/Performance and overall Performance.
5. Failover support in Oracle is difficult.	Failover support in SQL is much, much easier.
6. JDBC support is much better in Oracle.	JDBC support is little difficult in SQL Server.
7. Support ODBC.	Support ODBC.
8. Oracle is generally more proprietary and their main goal is to keep their customers locked-in.	SQL is ANSI-SQL '92 compliant, making it easier to convert to another ANSI compliant database, theoretically anyway (truth is every database has proprietary extensions).
9. Oracle natively supports proprietary connections, JDBC.	SQL natively supports ODBC, OLEDB, XML, XML Query, XML updates.
10. Most of Oracle is command-line. Oracle lacks in GUI support for Clients thus administrating Oracle is sluggish.	SQL Server is much easier to administrate, with GUI and command- line tools.
11. Oracle requires add-ons for transaction monitors, failover, etc.	SQL has COM+, uses NT clustering and generally has everything built-in
12. For Oracle Analysis Services is a separate purchase.	SQL Analysis Services is included (A very powerful OLAP server).
Performance and tuning
13a. The DBA can control sorting  and cache memory allocation.	In SQL Server, the DBA has no "real" control over sorting  and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.
b. Support  variable Page (block).	b. All pages (blocks) are always 8k and all extents are always 8 pages (64k). This means you have no way to specify larger extents to ensure contiguous space for large objects.
c. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, an invoice table can be partitioned into monthly partitions.	c. No range partioning of large tables and indexes.
d. Partitioned tables and partitioned indexes give performance and maintenance benefits and are transparent to the application.	d. There is no partitioning in SQL Server.
e. Oracle supports bitmap indexes.	e. There are no bitmap indexes in SQL Server.
f. Supports reverse key indexes.	f. There are no reverse key indexes in SQL Server.
g. Supports function-based indexes.	g. There are no function-based indexes in SQL Server.
h. Supports star query optimization.	h. There is no star query optimization. inSQL Server.
Object types
14a. Supports public or private synonyms.	a. No public or private synonyms.
b. Support independent sequences.	b. There is no such thing as independent sequence objects.
c. Support packages. i.e., collections of procedures and functions.	c. There are no packages;
d. Has "before" event triggers and  row triggers.	d. No "before" event triggers (only "after" event triggers) and no row triggers (only statement).
e. Has PL/SQL extension to support procedural programming.	e. No object types like in PL/SQL. .
Clustering technology
15a. In clustering technology, Oracle is light years ahead, Has Oracle Parallel Server/RAC -- two instances acting on the same data in active-active configurations.	a. SQL Server has nothing like Oracle Parallel Server/RAC
b. With the new version of Parallel Server in Oracle 9i, renamed as the Oracle Real Application Clusters, there is diskless contention handling of read-read, read-write, write-read, and write-write contention between the instances. This diskless contention handling is called Cache Fusion, and it means for the first time, any application can be placed in a cluster without any changes, and it scales upwards by just adding another machine to the cluster.	b. Microsoft has nothing like this.
Reliability
16a. All Edition of Oracle support transaction log files.	a. In SQL Server Standard Edition there is no ability to mirror the transaction log files. In Enterprise Edition there is a log shipping process that is not so reliable
b. Proper protection is made in Oracle for storing log files	b. If the logs fill up the disk, the database will crash hard. Sometimes this requires the server itself to be rebooted.
Installation
Oracle 9i supports Intel or compatible platforms, AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on. To install Oracle 9i under the Intel or compatible platforms: Hardware Requirements: Processor Pentium 166 MHz or higher Memory RAM: 128 MB (256 MB recommended) Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB Hard disk space 140 MB on the System Drive plus 4.5 GB for the Oracle Home Drive (FAT) or 2.8 GB for the Oracle Home Drive (NTFS) To install Oracle 9i Database under the UNIX Systems, such as AIX- Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, and Sun Solaris, you should have the following hardware: Hardware Requirements: Memory A minimum of 512 MB RAM Swap Space A minimum of 2 x RAM or 400 MB, whichever is greater Hard disk space 4.5 GB	Hardware Requirements Processor Pentium 166 MHz or higher Memory 32 MB RAM (minimum for Desktop Engine), 64 MB RAM (minimum for all other editions), 128 MB RAM or more recommended Hard disk space 270 MB (full installation), 250 MB (typical), 95 MB (minimum), Desktop Engine: 44 MB Analysis Services: 50 MB minimum and 130 MB typical English Query: 80 MB

Difference in storage

CATEGORY	MICROSOFT SQL SERVER	ORACLE
Number of columns	1,024	1,000
Row size	8,060 bytes, including 16 bytes to point to each text or image column	Unlimited (only one long or long raw allowed per row)
Maximum number of rows	Unlimited	Unlimited
Blob type storage	16-byte pointer stored with row. Data stored on other data pages	One long or long raw per table, must be at end of row, data stored on same block(s) with row
Clustered table indexes	1 per table	1 per table (index-organized tables)
Nonclustered table indexes	249 per table	Unlimited
Maximum number of columns in single index	16	32
Maximum length of column values within of an index	900 bytes	40% of block
Table naming convention	[[[Server.]database.]owner.] table_name	[schema.]table_name
View naming convention	[[[Server.]database.]owner.] table_name	[schema.]table_name
Index naming convention	[[[Server.]database.]owner.] table_name	[schema.]table_name

　

Difference In Data Types

ORACLE	MICROSOFT SQL SERVER
CHAR	charis recommended. char type columns are accessed somewhat faster than varchar columns because they use a fixed storage length.
VARCHAR2 and LONG	varcharor text. (If the length of the data values in your Oracle column is 8000 bytes or less, use varchar; otherwise, you must use text.)
RAW and LONG RAW	varbinaryor image. (If the length of the data values in your Oracle column is 8000 bytes or less, use varbinary; otherwise, you must use image.)
NUMBER	If integer between 1 and 255, use tinyint. If integer between -32768 and 32767, use smallint. If integer between -2,147,483,648 and 2,147,483,647 use int. If integer between –2^63 and 2^63 use bigint. If you require a float type number, use numeric (has precision and scale). Note Do not use float or real, because rounding may occur (Oracle NUMBER and SQL Server numeric do not round). If you are not sure, use numeric; it most closely resembles Oracle NUMBER data type.
DATE	datetime.
ROWID	Use the identity column type or the uniqueidentifier data type and the NEWID function.
CURRVAL, NEXTVAL	Use the identity column type, and @@IDENTITY global variable, IDENT_SEED() and IDENT_INCR() functions.
SYSDATE	GETDATE()
USER	USER

Differences in syntax

ACCESSING A TABLE IN…	ORACLE	MICROSOFT SQL SERVER
Your user account	SELECT * FROM STUDENT	SELECT * FROM USER_DB.STUDENT_ ADMIN.STUDENT
Other schema	SELECT * FROM STUDENT_ADMIN.STUDENT	SELECT * FROM OTHER_DB.STUDENT_ ADMIN.STUDENT

Join

JOIN	ORACLE	MICROSOFT SQL SERVER
Inner join or equi-join	SELECT * FROM authors AS a INNER JOIN publishers AS p WHERE a.city=p.city	SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city
Outer join(left,right,full)	SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT JOIN publishers p WHERE a.city = p.citySELECT a.au_fname, a.au_lname, p.pub_name FROM authors a RIGHT JOIN publishers p WHERE a.city = p.citySELECT a.au_fname, a.au_lname, p.pub_name FROM authors a JOIN publishers p WHERE a.city = p.city	SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.citySELECT a.au_fname, a.au_lname, p.pub_name FROM authors a RIGHT OUTER JOIN publishers p ON a.city = p.citySELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city
Self join	SELECT * FROM authors au1 JOIN authors au2 WHERE au1.zip =au2.zip	SELECT * FROM authors au1 INNER JOIN authors au2 ON au1.zip = au2.zip
Cross join	SELECT * FROM authors ,publishers	SELECT * FROM authors CROSS JOIN publishers

Creating Tables

ORACLE	MICROSOFT SQL SERVER
CREATE TABLE [schema.]table_name ( {col_name column_properties　 [default_expression] [constraint [constraint　 [...constraint]]]| [[,] constraint]} [[,] {next_col_name | next_constraint}...] ) [Oracle Specific Data Storage Parameters]Example:create table medicine(mid char(6),mname char(20),mtype char(20),manuName char(20),uprice number, minstock number(2),balstock number(5));	CREATE TABLE [server.][database.][owner.] table_name ( {col_name column_properties[constraint　 [constraint [...constraint]]]| [[,] constraint]} [[,] {next_col_name | next_constraint}...] ) [ON file group_name]　create table medicine(mid char(6),mname char(20),mtype char(20),manuName char(20),uprice numeric, minstock numeric(2),balstock numeric(5));

Creating Tables With SELECT Statements

ORACLE	MICROSOFT SQL SERVER
CREATE TABLE STUDENTBACKUP AS SELECT * FROM STUDENT	SELECT * INTO STUDENTBACKUP FROM STUDENT

Views

ORACLE	MICROSOFT SQL SERVER
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name [(column_name [, column_name]...)] AS select_statement [WITH CHECK OPTION [CONSTRAINT name]] [WITH READ ONLY]Create view view1 as select * from student;	CREATE VIEW [owner.]view_name [(column_name [, column_name]...)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION]　Create view view1 as select * from student;

Alter

ORACLE	MICROSOFT SQL SERVER
ALTER TABLE [schema.]table_name [add / modify] [(column_name newdatatype [, column_name new datatype]...)];Ex:ALTER TABLE emp add salary number(6);alter table pay_history add constraint ppri primary key(cid,tdate);	ALTER TABLE [owner.]table_name [alter] [(column_name newdatatype [, column_name new datatype]...)]　ALTER TABLE alter salary number(6);alter table pay_history add constraint ppri primary key(cid,tdate);

Insert

ORACLE	MICROSOFT SQL SERVER
INSERT INTO {table_name | view_name | select_statement} [(column_list)] {values_list | select_statement}　　　　　　　　　　　　　　　Example:insert into medicine values ('m0001', 'suresh', 'type1', 'ranbaxy',100,10,56);	INSERT [INTO] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n]) | view_name [ [AS] table_alias] | rowset_function_limited　 }{ [(column_list)] { VALUES ( { DEFAULT | NULL | expression　 }[,…n] ) | derived_table | execute_statement } } | DEFAULT VALUES　insert into medicine values ('m0001', 'suresh', 'type1', 'ranbaxy', 100, 10, 56)

Update

ORACLE	MICROSOFT SQL SERVER
UPDATE {table_name | view_name | select_statement} SET [column_name(s) = {constant_value | expression | select_statement | column_list | variable_list] {where_statement}Example:Update student set grade=’a’ where sid=200435326;	UPDATE {table_name | view_name | select_statement} SET [column_name(s) = {constant_value | expression | select_statement | column_list | variable_list] {where_statement}　Update student set grade=’a’ where sid=200435326;

Delete

ORACLE	MICROSOFT SQL SERVER
DELETE [FROM] {table_name | view_name | select_statement} [WHERE clause]　　　　　　　　　　　　　　　　Example:Delete from student where sid =200435330;	DELETE [FROM ] { table_name [ [AS] table_alias] WITH ( <table_hint_limited> […n]) | view_name [ [AS] table_alias] | rowset_function_limited　 }[ FROM {<table_source>} [, …n] ] [WHERE { <search_condition> | { [ CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name　 } ] } ] [OPTION (<query_hint> [,…n])]Delete from student where sid = 200435330;

　

Truncate

ORACLE	MICROSOFT SQL SERVER
TRUNCATE TABLE table_name [{DROP | REUSE} STORAGE]Example:Truncate table emp;	TRUNCATE TABLE table_name　Truncate table emp

Referential Integrity

CONSTRAINT	ORACLE	MICROSOFT SQL SERVER
PRIMARY KEY	[CONSTRAINT constraint_name] PRIMARY KEY (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters]	[CONSTRAINT constraint_name] PRIMARY KEY [CLUSTERED | NONCLUSTERED] (col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION]
UNIQUE	[CONSTRAINT constraint_name] UNIQUE (col_name [, col_name2 [..., col_name16]]) [USING INDEX storage_parameters]	[CONSTRAINT constraint_name] UNIQUE [CLUSTERED | NONCLUSTERED](col_name [, col_name2 [..., col_name16]]) [ON segment_name] [NOT FOR REPLICATION]
FOREIGN KEY	[CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [ON DELETE CASCADE]	[CONSTRAINT constraint_name] [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])] REFERENCES [owner.]ref_table [(ref_col [, ref_col2 [..., ref_col16]])] [ON DELETE CASCADE | No Action] [ON UPDATE CASCADE | No Action] [NOT FOR REPLICATION]
DEFAULT	Column property, not a constraint DEFAULT (constant_expression)	[CONSTRAINT constraint_name] DEFAULT {constant_expression | niladic-function | NULL} [FOR col_name] [NOT FOR REPLICATION]
CHECK	[CONSTRAINT constraint_name] CHECK (expression)	[CONSTRAINT constraint_name] CHECK [NOT FOR REPLICATION] (expression)

Stored Procedures

ORACLE

MICROSOFT SQL SERVER

CREATE OR REPLACE PROCEDURE [user.]procedure [(argument [IN | OUT] datatype [, argument [IN | OUT] datatype] {IS | AS} block　　　　　　　　　create or replace procedure minstopro (mid1 in medicine.mid%type)as rem_stock number(5);cursor cur1 is select balstock from medicine where mid=mid1;beginopen cur1;loopfetch cur1 into rem_stock;if rem_stock=0 thenraise_application_error(-20004,'no stock');elsedbms_output.put_line('inserted ....');end if;exit when cur1%notfound;end loop;close cur1;end;

CREATE PROC[EDURE] procedure_name [;number] [ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,…n] [WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} ] [FOR REPLICATION] AS sql_statement […n]CREATE PROCEDURE au_info2@lastname varchar(30) = 'D%',@firstname varchar(18) = '%'ASSELECT au_lname, au_fname, title, pub_nameFROM authors a INNER JOIN titleauthor taON a.au_id = ta.au_id INNER JOIN titles tON t.title_id = ta.title_id INNER JOIN publishers pON t.pub_id = p.pub_idWHERE au_fname LIKE @firstnameAND au_lname LIKE @lastname

Triggers

ORACLE	MICROSOFT SQL SERVER
CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES AFTER INSERT OR UPDATE OR DELETE ON STUDENT_ADMIN.GRADE FOR EACH ROW BEGIN INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE, NEW_SSN, NEW_CCODE, NEW_GRADE) VALUES (USER, SYSDATE, :OLD.SSN, :OLD.CCODE, :OLD.GRADE, :NEW.SSN, :NEW.CCODE, :NEW.GRADE), END;	CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADES ON STUDENT_ADMIN.GRADE FORAFTER INSERT, UPDATE, DELETE AS INSERT INTO GRADE_HISTORY( TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE NEW_SSN, NEW_CCODE, NEW_GRADE) SELECT USER, GETDATE(), OLD.SSN, OLD.CCODE, OLD.GRADE, NEW.SSN, NEW.CCODE, NEW.GRADE FROM INSERTED NEW FULL OUTER JOIN DELETED OLD ON NEW.SSN = OLD.SSN

Cursor

OPERATION	ORACLE	MICROSOFT SQL SERVER
Declaring a cursor	CURSOR cursor_name [(cursor_parameter(s))] IS select_statement;	DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,…n]]]
Opening a cursor	OPEN cursor_name　[(cursor_parameter(s))];	OPEN cursor_name
Fetching from cursor	FETCH cursor_name INTO variable(s)	FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM] cursor_name [INTO @variable(s)]
Update fetched row	UPDATE table_name SET statement(s)… WHERE CURRENT OF cursor_name;	UPDATE table_name SET statement(s)… WHERE CURRENT OF cursor_name
Delete fetched row	DELETE FROM table_name　 WHERE CURRENT OF cursor_name;	DELETE FROM table_name　 WHERE CURRENT OF cursor_name
Closing cursor	CLOSE cursor_name;	CLOSE cursor_name
Remove cursor data structures	N/A	DEALLOCATE cursor_name

Functions

FUNCTION	ORACLE	MICROSOFT SQL SERVER
Convert character to ASCII	ASCII	Same
String concatenate	CONCAT	(expression + expression)
Convert ASCII to character	CHR	CHAR
Return starting point of character in character string (from left)	INSTR	CHARINDEX
Convert characters to lowercase (LOWER)	LOWER	Same
Convert characters to uppercase (UPPER)	UPPER	Same
Pad left side of character string	LPAD	N/A
Remove leading blanks	LTRIM	Same
Remove trailing blanks	RTRIM	Same
Starting point of pattern in character string	INSTR	PATINDEX
Repeat character string multiple times	RPAD	REPLICATE
Phonetic representation of character string	SOUNDEX	Same
String of repeated spaces	RPAD	SPACE
Character data converted from numeric data	TO_CHAR	STR
Substring	SUBSTR	SUBSTRING
Replace characters	REPLACE	STUFF
Capitalize first letter of each word in string	INITCAP	N/A
Translate character string	TRANSLATE	N/A
Length of character string	LENGTH	DATALENGTH or LEN
Greatest character string in list	GREATEST	N/A
Least character string in list	LEAST	N/A
Convert string if NULL	NVL	ISNULL

Aggregate Functions

FUNCTION	ORACLE	MICROSOFT SQL SERVER
Average	AVG	Same
Count	COUNT	Same
Maximum	MAX	Same
Minimum	MIN	Same
Standard deviation	STDDEV	STDEV or STDEVP
Summation	SUM	Same
Variance	VARIANCE	VAR or VARP

Transaction Management

ORACLE	MICROSOFT SQL SERVER
<stmts>SAVEPOINT s<stmts>ROLLBACK s;<Stmts>COMMIT　ExampleINSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ('LIT', 'Literature') / UPDATE DEPT_ADMIN.CLASS SET MAJOR = 'LIT' WHERE MAJOR = 'ENG'　 SAVEPOINT s UPDATE STUDENT_ADMIN.STUDENT SET MAJOR = 'LIT' WHERE MAJOR = 'ENG'ROLLBACKs; DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = 'ENG' COMMIT	BEGIN TRANSACTION<stmts>SET SAVEPOINT S<stmts>ROLLBACK s;<Stmts>COMMIT　　BEGIN TRANSACTIONINSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ('LIT', 'Literature') UPDATE DEPT_ADMIN.CLASS SET DEPT = 'LIT' WHERE DEPT = 'ENG' SET SAVEPOINT s UPDATE STUDENT_ADMIN.STUDENT SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' ROLLBACK s; DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = 'ENG’ COMMIT TRANSACTION

Locking

ORACLE

MICROSOFT SQL SERVER

Oracle provides two locking techniques for locking at row level, page level, table level 1.Select .. for update statement This stmt used to lock one row or many rows for updating. This is an exclusive lock ie. Once locked others cant access. Syntax SELECT [col1,..] from [table name] where clause for update [wait/ nowait]; Example SELECT * from emp where salary > 10000 for update nowait; 　 2.Lock statement To manually over ride Oracles default locking strategy by creating a data lock in specific modes. syntax LOCK TABLE [table name] in {share/exclusive/row share /row exclusive/ share row exclusive / share update} [nowait]; Share-share all data but prevent update Exclusive-don’t allow others to access data. row share-share rows which but no update by others is allowed. row exclusive-don’t allow access to particular rows(used when insert, update) share row exclusive-other cant lock table but access some rows in the table. share update-updated rows can be viewed by others but no update is allowed. Example LOCK TABLE emp in exclusive [nowait];

Locking and concurrency SQL Server has no multi-version consistency model, which means that "writers block readers and readers block writers" to ensure data integrity. In contrast, with Oracle, the rule is "readers don't block writers and writers don't block readers." This is possible without compromising data integrity because Oracle will dynamically re-create a read-consistent image for a reader of any requested data that has been changed but not yet committed. In other words, the reader will see the data as it was before the writer began changing it (until the writer commits). SQL Server's locking scheme is much simpler (less mature) and will result in a lot of delays/waits in a heavy OLTP environment. Also, SQL Server will escalate row locks to page level locks when too many rows on a page are locked. This locks rows which are uninvolved in any updates for no good reason In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes

Related articles

• Introducing SQL Server to MySQL Migration Tool from Oracle (infoq.com)