There are various kinds of Relational Database Management Systems (RDBMS) like Microsoft Access, Sybase, and MySQL, for the two most popular and widely used are Oracle and My SQL Server. Even though there are many resemblances between the two systems, there are also several of key differences. In this article, you will be taking a look at a lot in particular, in the areas of what their command language, management of transaction control and their company of database objects.
The prime difference between the two RDBMS is about the language they use. Both the systems use a version of SQL, or SQL, M SQL server uses Transact SQL, or T-SQL. It is considered as an extension of SQL originally developed by Sybase and used by Microsoft. PL/Sql or Programmed Language/ Sql is used by Oracle. Different languages and capabilities are available for both the languages. Handling variables, Stored procedures, and built-in-functions are is the main difference between the two language. There are things which cannot be done in MS SQL server like PL/SQL in Oracle grouping procedures into packages. PL/SQL is complex and more powerful but T-SQL is simple and easy to use.
A huge difference between Oracle and MS SQL Server is transaction control. The article’s reason for a transaction can be considered as a group of tasks or operations and it is a single unit. For example, an assortment of SQL queries changing records that all must be modified at the same time, and if failed to update any single set will lead to failure of records being updated.
Automatically, MS SQL Server will perform and commit each command/task single handedly, and it will be difficult or impossible to move back again changes if any mistakes are experienced along the way.
To effectively group statement the “BEGIN TRANSACTION” control is for declaring the start of a transaction and COMMIT statement is sued at the end. For ending the transaction, the COMMIT statement will write the changed data to disk.
ROLLBACK will just undo the changes done within a transaction. Data corruption can be avoided by ROLLBACK when used properly with error handling. But then after declaring a COMMIT statement it is not possible to undo using the ROLLBACK command.
Every database connection is treated as new transaction within an Oracle. Commands are issued and queries are executed after that memory changes takes place and nothing is committed until a COMMIT statement is issued. The next command issued after the COMMIT statement initiates a new transaction and the process starts again.
Organization of Database Objects
RDBMS organizes database objects is the last difference over here but MS SQL Server arranges all things, like tables, views, and techniques, by database names. Users are fixed to a login which is provided accesses to the particular database and its objects.
Each database has a private disk file on the server in SQL server. All database objects are arranged by schemas and is a subset collection of databases shared among all users and schemas.
There can be a limitation to each user for certain tables and schemas via roles and permissions and it is all shared.
Stay connected to CRB Tech for more technical optimization and other updates and information.