Comparison between Oracle and Sqlserver 2000

 Comparison between Oracle and Sqlserver 2000  SQL SERVER TECHNICAL LIMITATIONS

    By Faulkner, Kent (kent.faulkner_at_trane.com)

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. PERFORMANCE and TUNING

1. No control of sorting (memory allocation)
2. No control over SQL Caching (memory allocation)
3. 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.
4. 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.
5. MISSING OBJECT TYPES
6. No public or private synonyms
7. no independent sequences
8. no packages ie. collection of procedures and functions.
9. PROGRAMMING
10. Significant extensions to the ANSI SQL-92 standard which means converting applications to a different database later will be a challenge (code re-write).
11. No JAVA database engine as in Oracle
12. Stored Procedures are not compiled until executed (overhead).
13. No ability to read/write from external files from a stored procedure.
14. Oracle Pl/Sql is 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 Oracle it takes one statement grouping by the trunc(<datecolumn>,'month') function.
15. 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. Received on Thu Jun 14 2001 - 03:11:51 CDT