Oracle versus Microsoft Sqlserver 2000

 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

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 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 single 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.

 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 commited transactions is not released to  new transactions until the recycling of the transaction  log is complete (in a circular round-robbin manner).  This means if there is an uncommited 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 uncommited transaction to  halt database activity.

 4. PERFORMANCE and TUNING

1. 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.
2. No control over SQL Caching (memory allocation) in Sql Serv. This is controllable in Oracle.
3. 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.
4. 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 actally shows the difference in the internal     database technology between Oracle and MS 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

1. 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).
2. 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.
3. 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 itensive operations. SqlServer 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 adminstration 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 are 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(<datecolumn>,'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. 2 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 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.

 i. 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.

 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.

    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.

    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 Sql-Net.     This means Zero data loss in the case of a failover     whereas Sql Server's log shipping cannot avoid data loss     during the time gap.

    Sql Server's log shipping mechanism also happens     at the OS level, whereas Oracle's mechanism can take     place directly at the Sql-Net level where logs are     automatically applied to standbys without any     scripts or OS batch files, this mechanism in     Oracle is called managed standby.

    One deficiency of Oracle in the standby was that     datafiles, if created on the primary, had to be manually     created on the standby whereas Sql Server does this     automatically. However, in 9i, this deficiency is     fixed and data files are created automatically     at the standby.

    Another deficiency of Oracle in the standby is that     direct loads, if using the unrecoverable facility to     bypass redo logging, require the data files of the     primary database to be manually copied across to the     standby. This is not fixed in 9i. Sql Server's version     of log shipping and direct loads do not require this     copying across.

 8. CLUSTER TECHNOLOGY
    In clustering technology, in the case of Sql Server,     2 nodes cannot work on the same database, they "share     nothing". At the best, to utilize the power of both nodes,     the application must be manually spit up and redistributed     between the hosts, working on different sets of data, and     it is not possible to seamlessly scale upwards by adding     another node to the cluster in the case of Sql Server.     Most cluster configurations in Sql Server use the power     of only 1 node, leaving the other node to take over only     if there is a problem with the first node.

    In the case of Oracle Parallel server, it is possible to have     2 or more instances of the database on different nodes acting     on the SAME data in active-active configurations. Lock management     is handled by the Oracle Parallel server. With the new version of     Parallel Server in Oracle 9i, renamed as the Oracle real application     cluster (9i RAC), 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 clustering technology     of Oracle, which can best be described as "light years ahead".

 9. As of 2002, Oracle has 14 independant security evaluations,

    Microsoft Sql Server has one.

1. 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.

 11.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, and its many technical advantages.

 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), perhaps at  the departmental level.

 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 Mar 06 2002 - 00:35:31 CST