Programming differences

SQL server is ANSI SQL 92 compliant and it will be possible to convert the database into other database formats. However, it requires a code rewrite.  SQL server also has no inbuilt JAVA database engine as in Oracle and Java classes cannot be loaded and executed in the database itself.  This limits database security and scalability to Java applications.

In SQL server procedures are not compiled till the point of execution unlike in oracle where the packages and procedures are compiled before execution.  Additionally in Oracle 9i, it is possible to translate PL/SQL code into C code and compile the code to get performance gains for numeric intensive operations.

The read/write of external files from a stored procedure cannot be performed in SQL Server, whereas this can be done in Oracle.

SQL server uses cryptic commands for database administration. Oracle uses more intuitive commands.

Further Oracle uses “create or replace” for procedures and views. This is not possible in SQL.  The result is that in SQL Server one must drop the procedure or view first and recreate it. This makes for two commands in place of one.

When changes are made to a dependent object in oracle the procedure, function, package or view is marked as invalid. This does not happen in SQL and the user may get unexpected results from his program.

Oracle has a recompiling option that is missing in SQL. The “alter procedure compile” command is applicable to procedures, functions, packages and views in Oracle. SQL programming demands that the whole procedure, function, package or view is resubmitted.

Null values will not be indexed in Oracle whereas SQL will index all rows. This may prove to be an advantage in Oracle where large tables and columns are often null and you need to find non null values quickly. It can however, be a disadvantage if you need to find null values.  SQL Server on the other hand allows you to ignore nulls if you wish to and index them otherwise. There are no nasty table scans that can surprise you.

Finally Triggers in Oracle do not provide for transactional control.  SQL server allows commits and rollbacks. This can be potentially dangerous in transactions that fire the trigger. Moreover, SQL server triggers can start transactions of their own, unlike in Oracle.

Performance testing
The statistics is fine grained in Oracle and the user has greater control. Yet obtaining the statistical reports is a challenge.

The SQL query optimizer is more reliable. Query hints are never used. This makes performance testing easier in SQL.

However, both data bases have performance glitches and the programmer will have to fix them.

Concurrency model

The concurrency model between SQL and Oracle are very different. Oracle operates against Point-In-Time snapshots of data, but acquires them on demand. Therefore readers are never blocked.

SQL on the other hand provides for more choices of isolation/concurrency and for more aggressive settings that will block readers and more aggressive readers can block writers.

Standby database differences
SQL Server and Oracle both allow standby databases. However, there are a number of differences in the way these databases are created and handled.  A standby database is usually set up on a second server. Logs are applied to this database for any changes so that the standby can be activated if the primary database fails.

In SQL server the “failover server” gets activated after the “master” and the “msdb” databases are restored from the backup or copied from the primary server and all logs are applied.  This is because the new users/roles added to the primary are not carried over to the standby and backups have to be done of the master and the msdb on a regular basis. Oracle standby database gets automatically activated after the logs are applied and all user and role information is available both in the primary and in the standby.

SQL server’s log shipping mechanism happens at OS level, while Oracles mechanism happens at Net8 level and logs are automatically applied to standbys without any scripts or OS batch files. However Oracle standby does not automatically create data files in the standby even when it is created in the primary database. Oracle 9i has rectified this problem. All versions of SQL server perform this operation automatically.

Direct loads are also not possible in Oracle if the unrecoverable facility is being used to bypass redo logging.  The data files from the primary database will then have to be manually copied to the standby. This has not been sorted out even in 9i.  This is not a problem in SQL Server versions.

Positioning

SQL server is an application that is positioned between MS-Access and Oracle. The functionality, performance and scalability of the application are a testimonial to this assertion.  SQL Server creates work groups and associates members to a work group and access to a specific data set is defined.  However, Oracle is more advanced and makes for larger applications in OLTP and Data warehousing. The clustering features of Oracle are ideal for Application Service Providers on the Internet, who want small clusters of servers that can be added to over a period of time. Oracle therefore makes a more convincing argument for the enterprise.

Cluster Technology

SQL server cluster technology does not permit the sharing of a database by two nodes in a cluster. The application must manually spit up and redistribute between the hosts who work on different data sets. It follows that it is not possible to scale up by adding nodes to a cluster of SQL Servers. Moreover, SQL Server configurations demand that only the power of one node is used while the others are failover clusters that are activated when the active node fails.

Oracle on the other had offers a Parallel server technology and it is possible for two or more instances of a database to remain active on different nodes in an active-active configuration. Lock management is used by the Oracle Parallel server. Oracle 9i has been renamed as Oracle Real Application Cluster and the Cache Fusion is a diskless contention that allows read-read, read- write, write-read, and write-write contention between the instances and any application can be placed in the cluster without changes and upward scaling is wholly possible.

Replication differences:
SQL uses a simple replication of publisher-subscriber for transactional replication. This technology is slow because even when one table is being replicated, the entire transaction log is checked by the log reader agent and the transactional changes are applied to the subscriber.  In Oracle snapshot logs store the changes to a single table and this is copied across the logs. Archive logs are not checked.

Miscellaneous differences:

• Oracle has 14 independent security evaluation as against SQL Server’s one.
• SQL Server Enterprise manager is easy to use; Oracle Enterprise manager is very complex.
• Oracle has introduced XMLType datatype to provide native support to XML and there are XML generation and aggregation operators that increase throughput of XML processing. In SQL Server there is only a limited means of reading and writing to xml from the tables.
• Oracle information pool is free, whereas SQL Server information pool requires subscription.