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.
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.
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
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.
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.
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
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>
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.
CREATE TRIGGER TR_Trig ON Locations
FOR UPDATE
NOT FOR REPLICATION
AS
BEGIN
INSERT INTO LocationINR
SELECT LocationNO
,getdate()
FROM inserted
END
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.
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.
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
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;
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
RESTORE DATABASE MyDB2020 FROM MyDB2020Backups;
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.
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.
Linux is an open-source operating system. It is based on the Linux kernel and this operating system is a typical package in a Linux distribution....
Microservices vs monolithic architecture describes a major change in how IT addresses software development and which has been happily embraced by companies like Google, Amazon,...