Oracle versus Microsoft Sqlserver 2000
SQL SERVER TECHNICAL LIMITATIONS
By Faulkner, Kent (kent.faulkner_at_trane.com) USA
Updated by Havewala, Porus (porushh_at_bigpond.com) Australia
- 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 flavours 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 dont block writers and writers
dont 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.
3. Potential of long uncommited transactions HALTING database activity
In sql server 2K, a long uncommited transaction can stop other
transactions which queue behind it in the transaction log, and
this can stop all activity on the database,
Whereas in Oracle, if there is a long uncommited transaction, only
the transaction itself will stop when it runs out of rollback
space, because of the use of different rollback segments for
transactions.
4. PERFORMANCE and TUNING
- 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.
- No control over SQL Caching (memory allocation) in Sql Serv.
This is controllable in Oracle.
- No control over storage/space management to prevent fragmentation in
Sql Serv. 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.
- No range partioning of large tables and indexes in Sql Server,
whereas in Oracle a large 100 GB table 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.
- 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 task.
- A Sql-Server dba claimed that fully qualifying the name 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 actally
shows the difference in the internal database technology between
Oracle and MS.
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
- 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).
- 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.
- In Sql Server, stored Procedures are not compiled until
executed (overhead). In Oracle, packages and procs/functions
are compiled before execution. Also, in Oracle 9i it is possible
to translate Pl/Sql into C code and then compile/link the code,
which gives very good performance gains for numeric itensive
operations.
- In Sql server, there is no ability to read/write from external
files from a stored procedure. Oracle has this ability.
- Oracle Sql and Pl/Sql are more powerful and can do things better 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 a complicated way
in T-Sql by grouping on strings, in Oracle it takes one sql statement
grouping by the trunc(<datecolumn>,'month') function. A Sql Server
manager who saw this difference said "This method in Oracle is more
intuitive, it understands the dates, the method in Sql Server does not."
- 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. 2 commands, in
Oracle there is no need - a single command "create or replace" is
enough.
- In Oracle, a procedure/function/package/view is marked as invalid
if a dependant object changes. In Ms Sql 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.
- A recompile reuses the code that is in the Oracle database,
the actual command is "alter procedure <procedure name> compile".
This is applicable to procedures/functions/packages/views.
This concept of recompiling is not there in MS Sql server
where you have to resubmit the whole code if you want to
recompile a procdure.
- CLUSTER TECHNOLOGY
In clustering technology, Oracle is light years ahead, since
Sql server has nothing like Oracle Parallel server - 2 instances
acting on the SAME data in active-active configurations. And with
the new version of Parallel Server in Oracle 9i, renamed as the
Oracle real application cluster, 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. Microsoft has nothing like this.
- Oracle has 14 independant security evaluations, Microsoft Sql
Server has one.
- Sql server magazines and internet articles of the magazine
are only available with paid subscription. Whereas, Oracle
has given its magazine free for many years, all articles are
free on the internet, and the Oracle Technical network (OTN)
is also free on the internet.
10.Some people say Microsoft Sql Server tools, like Enterprise
manager, are easy to use. Oracle Enterprise Manager is a huge
tool and seems daunting to unexperienced people. This is
true to an extent, however ease of use cannot be compared
with the many features in Oracle, and its industrial-level
strength.
SUMMARY.
SQL Server is clearly positioned between MS-ACCESS and ORACLE in terms of
functionality, performance, and scalability. It makes a work group level
solution (small number of users with small amount of data). At the
departmental level it is okay.
Oracle is much more advanced and has more to offer for larger applications
with both OLTP and Data Warehouse applications. Its new clustering features
are ideal for Application service providers (ASPs) on the internet
who can now start with a cluster of 2 small servers and grow by just
adding a server when they need to. Besides, Oracle's multi-platform
capability makes it the most convincing argument for an enterprise.
Footnote:
Oracle is the first commercial Sql database and is 25 years old in 2002,
ie. it has been around since 1977. Larry Ellision the founder of Oracle
has been championing the Sql language before there was any company around
like Microsoft.
Received on Mon Feb 18 2002 - 03:05:16 CST
