Database Wars: MSSQL Server, Oracle PL/SQL, and MySQL

With so many databases in the market, the war of minds has begun and now is the right time to understand the differences and importance between the three most important relational databases in the market - Microsoft SQL Server, Oracle PL/SQL and MySQL. Relational database management systems have become a mainstay in the industry right now, and with so many options out there, it can be hard to know which one to choose.

Relational database management systems were introduced in the 1980s. This article focuses on the history and characteristics of three popular RDBMS in the industry: Microsoft SQL Server, Oracle, and MySQL. Microsoft SQL Server and Oracle are commercial software, while MySQL is an open source RDBMS.

Oracle was the first company to launch an RDBMS for commercial use in the 1980s. In the mid-1990s, Microsoft entered the market with SQL Server as a serious competitor to Oracle. MySQL, on the other hand, was originally developed and distributed as 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 subsequently by Oracle.

Language - Core of Communication

The backbone of any RDBMS is the language used to execute queries, and this is how their performance and implementation is affected. Although all three database management systems use a version called Structured Query Language (SQL). Microsoft SQL Server uses Transact-SQL (T-SQL), an extension to SQL originally developed by Sybase and used by Microsoft. Oracle uses PL/SQL (Procedural Language/SQL).

Both are different "flavors" or dialects of SQL, and the two languages ​​have slightly different syntax and functionality. The main difference between the two languages ​​is how they handle variables, stored procedures, and built-in functions. PL/SQL in Oracle can also combine procedures into packages, which cannot be done in MS SQL Server. PL/SQL may be more complex and powerful, while T-SQL is simpler and easier to implement.

On the other hand, MySQL uses a lightweight version of T-SQL combined with a procedural language closely related to SQL/PSM. However, MySQL's stored code objects are close to the ANSI standard, but they do not have the breadth and depth of T-SQL, Microsoft and Sybase's SQL.

Transaction Control

A transaction can be viewed as a set of operations performed as a single unit. For example, if the user tries to execute some SQL queries, either all of them are executed or none of them are executed. This is one of the major differences between Oracle and MS SQL Server in transaction control.

By default, MS SQL Server will execute and submit each command/task one by one and if any error occurs, it will be difficult or impossible to roll back the changes. The "BEGIN TRANSACTION" command is used to group statements correctly and declare the beginning of a transaction. A COMMIT statement can be used at the end. This COMMIT statement writes the changed data to disk and ends the transaction. Within a transaction, ROLLBACK will discard any changes made within the transaction block. After a COMMIT is issued, no changes can be rolled back beyond the COMMIT command.

In Oracle, each new database connection is considered a new transaction. As queries are executed and commands are issued, changes are made only in the database's memory and remain in the cache. No changes will be committed unless a COMMIT statement is explicitly given. After the commit, the next command issued actually starts a new transaction and the process starts over. This provides greater flexibility and also helps with error control, since no changes are committed to disk until the COMMIT command is explicitly executed.

In the case of MySQL, transaction support is easily compatible with InnoDB. InnoDB is MySQL's storage engine and comes with MySQL by default. It provides standard ACID-compliant transaction capabilities, as well as foreign key support.

Organization of database objects

The organization of database objects is also an important difference between these three databases. MS SQL Server organizes all objects such as tables, views and procedures by database name. MS SQL users are assigned to a login that is granted access to a specific database and its objects. In Microsoft SQL Server, each database has a private, non-shared disk file on the server.

In Oracle, all database objects are grouped by schema. A schema is actually a collection of subsets of the database structure that contains all database objects. Although they are shared, each user's roles and permissions are defined and can be restricted to specific schemas and tables.

In short, MySQL, Oracle, and SQL Server are all powerful RDBMS options. While there are many other differences in how they work "internally" and are implemented, they can be used in almost equivalent ways. Each can be used in different environments with different goals. The purpose may be the same, but the implementation is different.

The above is the detailed content of Database Wars: MSSQL Server, Oracle PL/SQL, and MySQL. For more information, please follow other related articles on the PHP Chinese website!