MSSQL vs Oracle

MSSQL and Oracle are relational databases. They both are identical but different in various ways. They both store data in an organized way for querying. MSSQL is a relational database management system of Microsoft while Oracle RDBMS is a relational database management system of Oracle corporation. Both these databases are widely used by many enterprises for creating robust applications. This article will describe the technical difference between both databases.

Summary

1. MSSQL Create Database vs Oracle Create Database
2. MSSQL Stored Procedure vs Oracle Stored Procedure
3. MSSQL Triggers vs Oracle Triggers
4. MSSQL Backup vs Oracle Backup
5. Restore in MSSQL vs Restore in Oracle
6. Conclusion

MSSQL Create Database vs Oracle Create Database

The create is the common command in any relational database management system. In MSSQL and Oracle, this creates a command that can be used to create a database, table functions, stored procedures, and many important things. In this example, the create command is used to create databases.

MSSQL Create Database Example

USE master;
GO
--Detach the MyTest database
sp_detach_db MyTest;
GO
CREATE DATABASE MyTest ON
	(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\MyTest_data.mdf'),
	(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\MyTest_log.ldf'),
	(FILENAME = 'c:\myFiles\ImpFiles')
FOR ATTACH;
GO

Oracle Create Database Example

In Oracle, the CREATE DATABASE SQL statement is a more standard method of creating any database. The CREATE DATABASE statement in Oracle requires some extra steps such as creating views on the data dictionary tables and placing regular PL/SQL packages. These steps can be performed by running programmed scripts.

Example:

CREATE DATABASE newdb
   USER SYS IDENTIFIED BY sys_password
   USER SYSTEM IDENTIFIED BY system_password
   LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,
           GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,
   MAXLOGFILES 7
   MAXLOGMEMBERS 7

The above two examples display the difference between both Oracle and MSSQL create database statements.

MSSQL Stored Procedure vs Oracle Stored Procedure

A stored procedure is a saved SQL code. The stored procedure is the most important element of a relational database management system. In MSSQL, users can write T-SQL code and save that code as a stored procedure. This code or stored procedure can be called or reused again and again. It is like automation of a particular task. You just have to enter values in the parameter and then it will be executed automatically to complete the given task.

MSSQL Stored Procedure Example

USE TestDB;  
GO  
CREATE PROCEDURE MyProce.uspGetEmpTestDB2   
    @LName nvarchar(70),   
    @FName nvarchar(70)   
AS   

    SET NOCOUNT ON;  
    SELECT FName, LName, Dept 
    FROM MyProce.vEmp 
    WHERE FName = @FName AND LName = @LName  
    AND EndDate IS NULL;  
GO 

Oracle Stored Procedure Example

Stored Procedures in Oracle support the code to be identified and stored in the database. This makes the code reuse more manageable and more effective. Procedures still hold the block format, but the DECLARE keyword is followed by PROCEDURE descriptions, which are alike except for the extra return type description for a function.

CREATE OR REPLACE PROCEDURE mynumbers (
  a_low IN   NUMBER,
  b_upp  IN   NUMBER)
AS
BEGIN
  FOR i IN a_low .. b_upp LOOP
    DBMS_OUTPUT.put_line(i);
  END LOOP;
END;
/
 
SET SERVEROUTPUT ON
EXECUTE mynumbers(3, 7);
3
4
5
6
7

PL/SQL procedure successfully completed.

SQL>

MSSQL Triggers vs Oracle Triggers

In any relational database management system, Trigger is just like a stored procedure that is event-driven. In MSSQL, triggers are special stored procedures that are implicitly or explicitly fired automatically in reply to a particular event, object, database, and other server events. MSSQL contains three types of triggers. These are Data manipulation language (DML) triggers, Data definition language (DDL) triggers, and Logon triggers.

MSSQL Trigger Example

CREATE TRIGGER TR_Trig ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
 
BEGIN
  INSERT INTO LocationINR
  SELECT LocationNO
    ,getdate()
  FROM inserted
END

Oracle Trigger Example

In Oracle, Triggers are just like stored procedures or functions. It can incorporate SQL and PL/SQL or Java statements to work as a system and can call stored procedures. Still, procedures and triggers vary to the extent that they are called. A procedure is called by a user, application, or trigger. On the other hand, Triggers are automatically fired by Oracle when a respective event takes place.

CREATE OR REPLACE TRIGGER show_sal_changes 
BEFORE DELETE OR INSERT OR UPDATE ON emp 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   salary_diff number; 
BEGIN 
   salary_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old sal: ' || :OLD.sal); 
   dbms_output.put_line('New sal: ' || :NEW.sal); 
   dbms_output.put_line('The Total Salary difference: ' || salary_diff); 
END; 
/

The compulsory BEFORE or AFTER keyword and the arbitrary FOR EACH ROW specifies the firing point of the trigger.

MSSQL Backup vs Oracle Backup

Backup is the most important utility of any relational database. If data is lost then everything is lost. One can say that backup is the oxygen of a relational database management system. Both MSSQL and PostgreSQL provide the backup utility that can be used to take a backup of the database and then it can also be used to restore the database. MSSQL allows database administrators to take a complete backup of a SQL Server database. It also allows creating a database backup or filegroup. The full recovery model backs up the transaction log of the database to create a log backup. This is also called a bulk-logged recovery model.

MSSQL Backup command example

BACKUP DATABASE MyDB2020
TO DISK='A:\SQLServerBackups\MyDB1.bak',
DISK='B:\SQLServerBackups\MyDB2.bak',
DISK='C:\SQLServerBackups\MyDB3.bak'
WITH FORMAT,
  MEDIANAME = 'MyDB2020StripedSet0',
  MEDIADESCRIPTION = 'Striped media set for MyDB2020 database';
GO

Oracle Backup command example

The backup is the most important utility of Oracle. It belongs to the database administrator. One can utilize the BACKUP command to back up the main or a copy of a database, tablespace, or control file. It can also be used to take a backup of the server parameter file, archived redo log file, or backup set.

BACKUP DATABASE
  FORMAT '/disk5/arch_bps/db_%U.bck' 
  TAG monthly
  KEEP UNTIL TIME 'SYSDATE + 365'
  RESTORE POINT Q3FY09;

Restore in MSSQL vs Restore in Oracle

Just like backup, the restore is one of the most valuable commands of any relational database management system. If you are working as a database administrator then backup and restore are two commands that are widely used in the database environment. If you have taken a backup of the database and now if you don’t know how to restore it then it is not a sign of a good database administrator.

In MSSQL, a restore situation is the method of restoring data or database from one or more backup files and then retrieving the database. The restore situations depend on the recovery pattern of the database. It is the method of copying data from a backup and using logged events to the data. Restore is what database administrators perform with backups. They always take the backup file and transfer it into a database

MSSQL Restore Example

RESTORE DATABASE MyDB2020
  FROM MyDB2020Backups;

Oracle Restore Example

In oracle, the RESTORE command is used to restore, verify, or test and preview RMAN backups. Usually, database administrators restore backups when a hardware/software malfunction has broken a prevailing datafile, control file, or archived redo log or before implementing a point-in-time restoration. This is not the proper time to be harsh, but recovery without backups is not recommended.

You understand that you should have examined your restoration plan and that you should regularly back up a damaged database before trying to restore it. In oracle, To restore a dynamic backup of a file is to build it and make it accessible to the Oracle database server. To redeem a reestablished datafile is to renew it by utilizing redo logs, that is, documents of modifications performed to the database after the backup was taken. The following example is displaying the use of a restore command.

RMAN> RESTORE ARCHIVELOG ALL DEVICE TYPE sbt PREVIEW;

The above command is the official example available in the Oracle documentation. The important thing here to note is that the RESTORE command restores complete backups with various parameters and database administrators can restore files to their default location or a separate location.

Conclusion

Maybe the most noticeable distinction between Oracle and MSSQL is the programming language they apply. Both use a version of SQL. MS SQL Server utilizes T-SQL, which is a continuation of SQL and Oracle, while uses SQL and PL/SQL. Both are distinct “essences” or accents of SQL and both languages have separate syntax and abilities. The main distinction between the two RDBMS is how they control variables, stored procedures, and functions.

   

About the Author

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.