Microsoft SQL Server vs. Oracle

There are many different relational database management systems (RDBMS) out there. You have probably heard about Microsoft Access, Sybase, and MySQL, but the two most popular and widely used are Oracle and MS SQL Server. Although there are many similarities between the two platforms, there are also a number of key differences. In this blog, I will be taking a look at several in particular, in the areas of their command language, how they handle transaction control and their organization of database objects.

Language

Perhaps the most obvious difference between the two RDBMS is the language they use. Although both systems use a version of Structured Query Language, or SQL, MS SQL Server uses Transact SQL, or T-SQL, which is an extension of SQL originally developed by Sybase and used by Microsoft. Oracle, meanwhile, uses PL/SQL, or Procedural Language/SQL. Both are different “flavors” or dialects of SQL and both languages have different syntax and capabilities. The main difference between the two languages is how they handle variables, stored procedures, and built-in functions. PL/SQL in Oracle can also group procedures together into packages, which can’t be done in MS SQL Server. In my humble opinion, PL/SQL is complex and potentially more powerful, while T-SQL is much more simple and easier to use.

Transaction Control

Another one of the biggest differences between Oracle and MS SQL Server is transaction control. For the purposes of this article, a transaction can be defined as a group of operations or tasks that should be treated as a single unit. For instance, a collection of SQL queries modifying records that all must be updated at the same time, where (for instance) a failure to update any single records among the set should result in none of the records being updated. By default, MS SQL Server will execute and commit each command/task individually, and it will be difficult or impossible to roll back changes if any errors are encountered along the way. To properly group statements, the “BEGIN TRANSACTION” command is used to declare the beginning of a transaction, and either a COMMIT statement is used at the end. This COMMIT statement will write the changed data to disk, and end the transaction. Within a transaction, ROLLBACK will discard any changes made within the transaction block. When properly used with error handling, the ROLLBACK allows for some degree of protection against data corruption. After a COMMIT is issued, it is not possible to roll back any further than the COMMIT command.

Within Oracle, on the other hand, each new database connection is treated as new transaction. As queries are executed and commands are issued, changes are made only in memory and nothing is committed until an explicit COMMIT statement is given (with a few exceptions related to DDL commands, which include “implicit” commits, and are committed immediately). After the COMMIT, the next command issued essentially initiates a new transaction, and the process begins again. This provides greater flexibility and helps for error control as well, as no changes are committed to disk until the DBA explicitly issues the command to do so.

Organization of Database Objects

The last difference I want to discuss is how the RDBMS organizes database objects. MS SQL Server organizes all objects, such as tables, views, and procedures, by database names. Users are assigned to a login which is granted accesses to the specific database and its objects. Also, in SQL Server each database has a private, unshared disk file on the server. In Oracle, all the database objects are grouped by schemas, which are a subset collection of database objects and all the database objects are shared among all schemas and users. Even though it is all shared, each user can be limited to certain schemas and tables via roles and permissions.

In short, both Oracle and SQL Server are powerful RDBMS options. Although there are a number of differences in how they work “under the hood,” they can both be used in roughly equivalent ways. Neither is objectively better than the other, but some situations may be more favorable to a particular choice. Either way, Segue can support these systems and help to make recommendations on how to improve, upgrade, or maintain your key mission-critical infrastructure to make sure that you can keep your focus on doing business.

Platform dependency

SQL Server is only operable on the Windows platform, a major limitation for it to be an enterprise solution. Oracle is available on multiple platforms such as Windows, all flavors 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

Performance and tuning

1. In SQL Server, the DBA has no "real" control over sorting and cache memory allocation. The memory allocation is decided only globally in the server properties memory folder, and that applies for ALL memory and not CACHING, SORTING, etc.
2. 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.
3. No range partioning of large tables and indexes. In Oracle, a large 100 GB table can be seamlessly partitioned at the database level into range partitions. For example, 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.
4. There is no partitioning in SQL Server.
5. There are no bitmap indexes in SQL Server.
6. There are no reverse key indexes in SQL Server.
7. There are no function-based indexes in SQL Server.
8. There is no star query optimization in SQL Server.

Object types

Here are some object types missing in SQL Server that exist in Oracle.

1. You cannot declare public or private synonyms.
2. There is no such thing as independent sequence objects.
3. There are no packages; i.e., collections of procedures and functions.
4. No "before" event triggers (only "after" event triggers) and no row triggers (only statement).
5. No object types like in PL/SQL.

PL/SQL versus T-SQL

1. In T-SQL there are significant extensions to the ANSI SQL-92 standard which means converting applications to a different database later will be a code challenge re-write. The INNER JOIN, LEFT OUTER, RIGHT OUTER JOIN syntax differs from the classic JOIN.
2. Stored procedures in SQL Server are not compiled until executed (which means overhead, late binding and finding errors at execution time only!).
3. No ability to read/write from external files from a stored procedure.
4. PL/SQL has many DBMS system packages, where T-SQL relies only on a limited number of extended and system stored procedures in the master database.
5. PL/SQL is better in error exception handling, and there is no such thing as exceptions in T-SQL (T-SQL uses @@error -- not elegant!).
6. T-SQL does not have the MINUS operator, that makes finding schema differences more difficult in SQL Server.
7. In SQL Server there is no "dead connection detection". Clients who lose their connection may still hold locks until a DBA is notified to kill their server side processes.
8. In SQL Server there is no such thing as SQL*NET aliases/service names! This means applications have to hard code the actual server name into their apps, making it difficult to move databases later to load balance across servers.

Reliability

1. In SQL Server Standard Edition there is no ability to mirror the transaction log files. In Enterprise Edition there is a log shipping process that is not so reliable.
2. If the logs fill up the disk, the database will crash hard.
3. Sometimes this requires the server itself to be rebooted.