Do you want to have diff between Oracle & SQL Server?
The following article compares Oracle versus Microsoft SQL Server 2000.
It highlights most of the SQL Server technical limitations, and in the
same time gives the corresponding Oracle solutions to these limitations.
The article could be helpful, to anyone trying to make comparison or
evaluation to any of the both products.
The article was written by Faulkner, Kent - USA and updated by Havewala,
Porus - Australia.
1. SINGLE PLATFORM DEPENDANCY
SQL Server is only operable on the Windows platform, and this is a major
limitation for it to be an enterprise solution. Oracle is available on
multiple 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. The multi-platform nature of Oracle makes it a true enterprise
solution.
2. LOCKING / 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 ff
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.
3. POTENTIAL OF LONG UNCOMMITED TRANSACTIONS HALTING DATABASE ACTIVITY
In SQL Server 2000, a long uncommitted transaction can stop other
transactions which queue behind it in the single transaction log, and
this can stop all activity on the database, whereas in Oracle, if there
is a long uncommitted transaction, only the transaction itself will stop
when it runs out of rollback space, because of the use of different
rollback segments for transactions.
Oracle allocates transactions randomly to any of its multiple rollback
segments and areas inside that rollback segment. When the transaction is
committed, that space is released for other transactions, however Sql
server allocates transactions sequentially to its single transaction
log, the space occupied by committed transactions is not released to new
transactions until the recycling of the transaction log is complete (in
a circular round-robin manner).
This means if there is an uncommitted transaction in the middle, and the
transaction log cannot grow by increasing the file size, no new
transactions will be allowed. This is the potential of a single
uncommitted transaction to halt database activity.
4. PERFORMANCE and TUNING
a. No control of sorting (memory allocation) in SQL Server. Oracle can
fully control the sort area size and allows it to be set by the DBA.
b. No control over SQL Caching (memory allocation) in SQL Server. This
is controllable in Oracle.
c. No control over storage/space management to prevent fragmentation in
SQL Server. 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. In Oracle, this is fully
configurable.
d. No range partioning of large tables and indexes in SQL Server,
whereas in Oracle a large table (eg. 100 GB or more) can be seamlessly
partitioned at the database level into range partitions, for eg. an
invoice table can be partitioned into monthly partitions.
Such partitioned tables and partitioned indexes give performance and
maintenance benefits and are transparent to the application.
e. No Log miner facility in SQL Server. Oracle 8i and 9i supply a Log
Miner which enables inspection of archived redo logs. This comes free
with the database. But in the case of SQL Server, external products from
other companies have to be purchased to do this important DBA task.
f. A SQL Server DBA claimed that fully qualifying the name of an object
in SQL Server code would lead to performance gains of 7% to 10%. There
are no dictionary performance problems like that in Oracle. Oracle would
have some gains if it fully qualified all names - say 0.01 percent.
This actually shows the difference in the internal database technology
between Oracle and Microsoft and implies that the technology of
resolving object names via the dictionary is more advanced in the case
of Oracle, ie. Oracle seems to better access its internal dictionary and
resolve names, unlike SQL server.
5. MISSING OBJECT TYPES IN SQL SERVER
a. No public or private synonyms b. No independent sequences c. No
packages ie. collection of procedures and functions.
6. PROGRAMMING
a. Significant extensions to the ANSI SQL-92 standard in SQL Server,
which means converting applications to a different database later will
be a challenge (code re-write).
b. Sql Server has no inbuilt JAVA database engine as in Oracle. In
Oracle, Java classes can be loaded and executed in the database itself,
thus adding the database's security and scalability to Java applications.
c. In SQL Server, stored Procedures are not compiled until executed
(overhead). In Oracle, packages and procs/functions are compiled before
execution.
In Oracle 9i it is also possible to translate Pl/Sql into C code and
then compile/link the code, which gives very good performance gains for
numeric intensive operations. SQL Server has no such ability.
d. In SQL server, there is no ability to read/write from external files
from a stored procedure. Oracle has this ability.
e. SQL Server uses cryptic commands for database administration like:
exec sp_addrolemember N'db_datareader', N'davidb' go
This is to add the user davidb to the role db_datareader.
On the other hand, Oracle uses standard English-like SQL commands to do
the same:
grant db_datareader to davidb;
This one statement does all, in simple English, what the cryptic SQL
Server command does.
f. Oracle SQL and PL/SQL syntax is more powerful and can do things more
intuitively than Microsoft Transact-SQL. Try to sum up a column by each
month, and show the totals for the month, in SQL Server you do it in
T-SQL by grouping on strings, in Oracle it is possible to do this
grouping by the trunc(,'month') function. This method in Oracle is more
intuitive, it understands the dates, the method in SQL Server does not.
g. In SQL Server, you cannot issue a "create or replace" for either
procedures or views, in Oracle you can. This one facility simplifies
code writing, since in Sql Server the procedure or view must be dropped
first if present and then recreated ie. two commands, in Oracle there is
no need - a single command "create or replace" is enough.
h. In Oracle, a procedure/function/package/view is marked as invalid if
a dependant object changes. In SQL Server there is no concept of an
invalid procedure, it will run but give unexpected results. The former
is more suitable for change control and preventing unexpected errors.
i. A recompile reuses the code that is in the Oracle database, the
actual command is "alter procedure compile". This is applicable to
procedures/functions/packages/views. This concept of recompiling is not
there in SQL Server where you have to resubmit the whole code if you
want to recompile a procedure.
j. Triggers in Oracle do not allow transactional control ie.
commit/rollback/savepoint statements. Whereas, triggers in SQL Server
allow commits/rollbacks, which is potentially dangerous and can cause
problems with transactions which fire the trigger.
Triggers in SQL Server also can start transactions of their own which is
not very good and shows lack of maturity of the language.
7. STANDBY DIFFERENCES
SQL Server and Oracle have differences regarding standby databases. A
standby is a database set up on a second server and to which logs are
applied ie. all database changes, so that the standby can be activated
and used in the case of a failover.
a. In the case of Sql server, when there is a failover, the "master" and
"msdb" databases have to be restored from backup or copied over from the
primary to the standby and then the standby is activated after all logs
are applied. In Oracle, there is no need for a restore to be done, the
standby can be activated at any time after all logs are applied.
This difference exists because of the fact that in SQL server, new
users/roles added to the primary are not carried over to the standby
(these users/roles go in the master/msdb) and backups have to be done
continuously of the master and msdb, these backups are then restored
when the time comes for a failover.
In the case of Oracle, users/roles when created in the primary are
automatically carried over to the standby. So when the failover time
arrives, all that is needed is to activate the standby.
b. In the case of Sql Server, if the standby is opened as read only, to
switch it back to standby again, a restore from backup has to be done.
In the case of Oracle, from 8i version onwards, if a standby database is
opened as read only, it can be reopened as a standby without restoring
from backup.
c. The time delay to apply logs between the primary and the standby can
be varied, but it can never be 0 minutes in the case of SQL Server.
In the case of Oracle, in 9i it is possible to have logs applied
simultaneously to the primary as well as standby, using Net8 protocol.
This means zero data loss in the case