Difference between Oracle and Microsoft SQL

There are many relational database management systems but Oracle and Microsoft SQL are the most widely used ones; others are Sybase, Microsoft Access and MySQL.

Oracle and MS SQL have many similarities but they also have differences. They have difference in transaction control, language and the way the database objects have been organized.

One of the main differences in these two relational database management systems is the command language.  Although they both use query language, MS SQL uses Transact SQL which is mainly an extension SQL which was initially developed by Sybase and used by Microsoft.  On the other hand, Oracle uses Procedural Language/SQL, as its command language.  The two languages use different dialects of SQL and they both have capabilities and syntax.

The other main difference is in their transaction control. Transaction is the group is tasks that should be used as one unit. For example collecting SQL queries, adjusting records that should all be updated at once or at the same time, if one of the records fails to update, then the end result should be that none of the records will update.

When it comes to this area control, MS SQL will perform a control individually and it will be difficult to redo any transaction that has been done especially if there are errors that have occurred during the process.  When the transaction is ready you use the ‘BEGIN TRANSACTION’ command to start the planned transaction. When it is completed, you use the ‘COMMIT’ command to end the transaction. There is the ‘ROLLBACK’ command is for discarding transaction changes and also gives a degree of protection against data corruption during the transaction.

On the other hand, Oracle treats every new database connection and a new transaction. Here commands are given as queries are executed and when there are changes to be made, they can only be made in memory and nothing is committed till such a time when there is an explicit ‘COMMIT’ statement. Once the COMMIT is given the next command starts a new transaction and the process starts all over again.

The other difference is in the organization of the database objects. When it comes to Oracle, the database objects are organized or grouped in schemas, which act a portion or subset of all the objects. The schemas and users have shared database objects, though each user can be limited to certain schemas depending on the different roles and permissions granted.  On the other hand, the MS SQL organizes all objects by database names. Users log in using permitted access to specific database. This therefore makes it easy for users to log in using their own access and therefore can be private to a certain level.

These relational database management systems may have differences but they also have similarities which make them most preferred options amongst many. In real sense none is particularly better than the other, but depending on your needs you may prefer to use one relational database management systems over the other.

It may be important to take a look at the differences before using one of these systems just to ensure that you pick what is comfortable for your use. This will help in reducing any disappointments while operating the servers. Your options can also depend on the size of the server.

This entry was posted in Blog. Bookmark the permalink. Both comments and trackbacks are currently closed. Edit