Oracle versus Microsoft Sqlserver 2000
SQL SERVER TECHNICAL LIMITATIONS
By Faulkner, Kent (kent.faulkner_at_trane.com) USA
- 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. PERFORMANCE and TUNING
- No control of sorting (memory allocation)
- No control over SQL Caching (memory allocation)
- No control over storage/space management to prevent fragmentation. 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.
- No range partioning of large tables and indexes eg. 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. 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 task.
- A Sql-Server dba claimed that fully qualifying the name in 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. That shows the
difference in the internal database technology between Oracle and MS.
- MISSING OBJECT TYPES
- No public or private synonyms
- no independent sequences
- no packages ie. collection of procedures and functions.
- PROGRAMMING
- Significant extensions to the ANSI SQL-92 standard which means
converting
applications to a different database later will be a challenge (code
re-write).
- 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.
- Stored Procedures are not compiled until executed (overhead).
- No ability to read/write from external files from a stored procedure.
- 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.
In Oracle it takes one sql statement grouping by the
trunc(<datecolumn>,'month') function.
- 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, in Oracle there is no need.
- 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.
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).
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 Wed Jan 16 2002 - 17:59:05 CST
