Many Similarities, But Different?
RDBMSs:
There are allot of different relational database management systems (RDBMS) out there. You may have heard about Microsoft Access, Sybase, and MySQL but the two most famous and widely used are MS SQL Server and Oracle. Although there are many common things between the two RDBMSs there are also a number of key differences. In this blog, I will take a look at several in peculiar, in the areas of their command language, how they manage transaction control and their management of database objects.
LANGUAGE:
Perhaps the foremost obvious distinction between the 2 RDBMS is the language they use. Though each 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 2 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 not be done in MS SQL Server. In my opinion, PL/SQL is complex and probably more powerful, whereas T-SQL is much more simple and easier to use.
Transaction Control
Another one among the foremost variations between Oracle and MS SQL is transaction control. For the aim of this article, a transaction can be defined as a block of operations or task that ought to be treated as one entity. For example, a set of SQL queries altering records that every one should be updated at the same time, where (for example) a failure to update one record among the group should conclude none of the records being updated. As a default manner, MS SQL Server will execute and commit every command or task individually, and it will be difficult or maybe not possible to roll back the changes if any errors are encountered along the way. To properly group statements, the "BEGIN TRANSACTION" command is used to declare the start of a transaction and either a COMMIT statement is used at the end. This COMMIT statement will write the modified data to disk and end the transaction. During the transaction, the ROLLBACK command will reject any changes made within the transaction block. When properly used with error handling the ROLLBACK permits for a few degree of safety against data corruption. When a COMMIT is issued, it's not possible to roll back any further than the COMMIT command.
Within Oracle, on the other hand, each new database connection is considered as a new transaction. As queries are executed and commands are issued, changes are made solely in memory and nothing is committed until an explicit COMMIT command is given (with a few exceptions to DDL commands, which include "implicit" commits, and are committed immediately). After the COMMIT the next command issued primarily starts a new transaction, and the process begins again. This provides much better flexibility and assists for error control as well, as no changes are committed to disk unless or until the DBA explicitly issues the command to do so.
Within Oracle, on the other hand, each new database connection is considered as a new transaction. As queries are executed and commands are issued, changes are made solely in memory and nothing is committed until an explicit COMMIT command is given (with a few exceptions to DDL commands, which include "implicit" commits, and are committed immediately). After the COMMIT the next command issued primarily starts a new transaction, and the process begins again. This provides much better flexibility and assists for error control as well, as no changes are committed to disk unless or until the DBA explicitly issues the command to do so.
Organization of Database Objects
The last distinction i would like to talk about is how the RDBMS organizes database objects. MS SQL Server organizes all objects, like tables, views, and procedures, by database names. Users are assigned to a login that is granted access to a 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 shared among all schemas and users. Although it's all shared, each user can be restricted to certain schemas and tables via roles and permissions.
To conclude, both Oracle and Microsoft SQL Server are powerful RDBMS choices. Though there are a number of differences in how they work "under the hood", they can both be used in roughly the same ways. Neither is objectively higher than the other, however some situations help to make recommendations on how to improve, upgrade, or maintain your key mission-critical infrastructure.
So the above differences will help you know the answer of your question of how one is so renowned and the other one is still on the way to progression.
Comments
Post a Comment