With so many databases in the market, the mind wars have begun and it is the right time to understand the difference and importance of top 3 relational databases in the market – Microsoft SQL Server, Oracle PL/SQL, and MySQL. The relational database management systems have currently become the backbone of the industry and with so many options available, it is difficult to figure out which one to choose.
The relational database management systems was introduced in 1980’s. This article is focussing on exploring the history and features of three popular RDBMS in the industry: Microsoft SQL Server, Oracle, and MySQL. Microsoft SQL Server and Oracle being the commercial software, MySQL is the open-source RDBMS.
Oracle was the first company to bring out RDBMS for commercial use in the 1980s. In the mid-1990s, Microsoft entered the market with SQL Server as a serious competitor for Oracle. MySQL, on the other hand, was originally developed and distributed as an open-source software. MySQL was first released in 1995, and the windows version was released in 1998. In 2008, it was acquired by SUN and thereafter by Oracle.
The backbone of any RDBMS is the language used to execute the queries and that is how their performance and implementation is impacted. Although all three database management systems use a version of Structured Query Language, or SQL. Microsoft SQL Server uses Transact-SQL, or T-SQL, which is an extension of SQL originally developed by Sybase and used by Microsoft. Whereas, Oracle, meanwhile, uses PL/SQL, or Procedural Language/SQL.
Both are different “flavors” or dialects of SQL and both languages have slightly 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. PL/SQL can be a bit more complex and potentially more powerful, while T-SQL is much more simple and easier to implement.
MySQL, on the other hand, uses the light version of T-SQL and also combines procedural language that closely relates to SQL/PSM. However MySQL’s stored code-objects are close to ANSI standards, but again, they don’t have the breadth and depth of T-SQL, Microsoft and Sybase’s proprietary extension to SQL.
A transaction can be said to be a group of operations that are executed as a single unit. For example, if the user is trying to execute some SQL queries, then either all are executed or none. This is one of the major differences between Oracle and MS SQL Server in reference to transaction control.
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 come along the way. “BEGIN TRANSACTION” command is used to properly group the statements and declare the beginning of a transaction, and a COMMIT statement can be 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. After a COMMIT is issued, it is not possible to roll back any further, than the COMMIT command.
Whereas in Oracle, each new database connection is taken as a new transaction. As queries get executed and commands are issued, the changes are made only in memory of the database and remain in cache. Nothing is committed until an explicit COMMIT statement is given. 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 COMMIT command is explicitly executed.
In the case of MySQL, the support of transactions is easily compatible with InnoDB. InnoDB is a storage engine for MySQL and by default is available with MySQL. It provides the standard ACID-compliant transaction features, along with foreign key support.
The organization of database objects also is a big differentiator in these 3 databases. MS SQL Server organizes all objects, such as tables, views, and procedures, by database names. The MS SQL users are assigned to a login, which is granted accesses to the specific databases and also to its objects. In Microsoft SQL Server, each database has a private, unshared disk file on the server.
Whereas in Oracle, all the database objects are grouped by schemas. The schemas are nothing but the database structures that are a subset collection of database objects. All the database objects are shared among all schemas and users. Even though it is all shared, the roles and permissions for each user are defined and the same can be limited to certain schemas and tables.
In short, all three, MySQL, Oracle and SQL Server are powerful RDBMS options. Although there are a number of other differences in how they work “under the hood” and implemented, they can be used in roughly almost equivalent manner. Each can be used with different objective in different environment. The purpose may be same, but the implementation varies.