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 ~ Asked by odelia, On: Jun 10, 2008