Comparison of Oracle & SQL Server

1. Programming

Oracle: PL/SQL is the language used for programming

SQL Server: T-SQL is the language used for programming

2. ROWID

Oracle:

Select Rowid, A.* from Temp;

ROWID	NumID
AAAJWTAAWAAB+BiAAA	10
AAAJWTAAWAAB+BiAAB	20

SQL Server:

CREATE TABLE Temp (num INT)
go

INSERT INTO Temp VALUES (10), (20)
go

SELECT %%physloc%% AS [%%physloc%%],
       sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot],
       num 
  FROM Temp
go

Output

%%physloc%%	File:Page:Slot	NUM
0x2926020001000000	(1:108786:0)	10
0x2926020001000100	(1:108786:1)	20

3. Dual

Oracle: Dual Table is made available by default.

Select * from Dual;

SQL Server: Dual table can be created.

--Solution 1
Create Table Dual (Dummy varchar(1))
go

Insert Into Dual (Dummy) Values (‘x’)
go

--Solution 2
SELECT 'X' AS DUMMY INTO DUAL
go

--Solution 3
CREATE VIEW DUAL AS SELECT 'X' AS DUMMY
go

--and then use as following
Select * from Dual
go

4. Minus

Oracle:

Select column1, column2 from table1
Minus
Select column1, column2 from table2;

SQL Server:

Select column1, column2 from table1
Except
Select column1, column2 from table2
go

5. Decode

Oracle:

Select DECODE(Rankings, 'First',  1,
                        'Second', 2,
                        'Third',  3, 0) as Ranks
  From StudRanks;

SQL Server:

Select Case when Rankings = 'First'  then 1
            when Rankings = 'Second' then 2
            when Rankings = 'Third'  then 3
            else 0
       end as Ranks
  From StudRanks
Go

Note: Case statement is available in Oracle too.

6. DBMS_Output.Put_Line

Oracle: DBMS_Output.Put_Line is used to display values of passed parameters on the console.

SQL Server: Print is used to display values of passed parameters on the console.

7. Varchar2

Oracle:

Declare
  V_name varchar2(20);
Begin
  V_name := 'TechOraFAQ.in';
  DBMS_Output.Put_Line(V_Name);
End;

SQL Server:

Begin
  Declare @V_name varchar(20);
  Set @V_name = 'TechOraFAQ.in';
  Print @V_Name;
End
go

8. String Concatenation

Oracle: || is used as string concatenation operator.

Select 'TechOraFAQ.' || 'in' as lnk from dual;

SQL Server: + is used as string concatenation operator.

--dual table created in Point # 3 above
Select 'TechOraFAQ.' + 'in' as lnk
go

9. Sysdate & SystemTimeStamp

Oracle:

Select sysdate, SystemTimeStamp from dual;

SQL Server:

Select getdate(), SYSDATETIME()
GO

10. Variables

Oracle:

Declare
  v_sales     number(10, 2);
  c_pi        CONSTANT double := 3.1415;
  v_name      varchar2(25);
  v_address   varchar2(100);
  v_counter   binary_integer := 0;
  v_greetings varchar2(20) DEFAULT 'Have a Good Day';
  v_dt        Date;
begin
  v_dt        := To_Date('01-Jan-2021', 'DD-Mon-YYYY');
  v_sales     := 1000.55;
  v_name      := 'Ramesh Jha';
  v_greetings := 'Happy Birthday';
  v_counter   := 1;
  v_dt        := Sysdate;
  Dbms_output.put_line(v_dt);
  Dbms_output.put_line(v_sales);
  Dbms_output.put_line(v_name);
  Dbms_output.put_line(v_greetings);
  Dbms_output.put_line(v_counter);
  Dbms_output.put_line(c_pi);
end;

SQL Server:

Begin
  Declare @v_sales     Decimal(10, 2);
  Declare @c_pi        double precision = 3.1415;
  Declare @v_name      nvarchar(25);
  Declare @v_address   nvarchar(100);
  Declare @v_counter   binary_integer = 0;
  Declare @v_greetings nvarchar(20) = 'Have a Good Day';
  Declare @v_dt        DateTime;

  Set @v_dt        = Convert(DateTime,'2021-01-01');
  Set @v_sales     = 1000.55;
  Set @v_name      = N'Ramesh Jha';
  Set @v_greetings = N'Happy Birthday';
  Set @v_counter   = 1;
  Set @v_dt        = getdate();

  Print @c_pi
  Print @v_sales
  Print @v_name
  Print @v_greetings
  Print @v_counter
  Print @v_dt
end
go

11. Commit

Oracle: Auto Commit is by default OFF. So, don’t forget to execute commit after modifying the data in the tables.

SQL Server: Auto Commit is by default ON. So be cautious while executing Insert, Update & Delete statements.

12. Create select

Oracle:

Create table Temp_EMP as
  Select * from EMP;

SQL Server:

Select * into Temp_EMP from EMP
Go

13. Triggers 

Oracle: After and before triggers are available. Insteadof trigger is available.

SQL Server: Only After triggers are available. Insteadof trigger is available.

14. Packages

Oracle: Packages can be created in Oracle.

SQL Server: Packages are not supported in SQL Server. Here the procedures and functions are to be created and maintained individually. In order to group them together one can have a common prefix before each procedure and function that are to be grouped together.

15. ROWNUM

Oracle:

Select rownum, e.* from emp e;

SQL Server:

Select row_number() over(order by <column>) as rownum, e.*
  from emp
go

16. UTL_FILE

Oracle: UTL_FILE can be used to read from & write to text files using PL/SQL methods.

SQL Server: SSIS can be used for these purposes.

17. UTL_SMTP

Oracle: UTL_SMTP

SQL Server: sp_send_dbmail

18. Cursors

ORACLE:

CREATE OR REPLACE PROCEDURE sp_disp_date IS
  v_date DATE;
  CURSOR c1 IS
    SELECT SYSDATE FROM dual;
BEGIN 
  OPEN c1;
  FETCH c1 INTO v_date;
  CLOSE c1;   
  DBMS_OUTPUT.PUT_LINE(v_date);
END;

--Execution of procedure
EXEC sp_disp_date;

--Output
16-Mar-22

SQL Server:

CREATE PROCEDURE sp_disp_date AS
  DECLARE @v_date DATE

  DECLARE c1 CURSOR FOR
    SELECT GETDATE()

  OPEN c1
  FETCH c1 INTO @v_date
  CLOSE c1
  DEALLOCATE c1   

  PRINT @v_date
GO

--Execution of procedure
EXEC sp_disp_date

--Output
2022-03-16

19. Sequence

Oracle:

CREATE SEQUENCE id_seq
  START WITH 1
  MINVALUE 1
  MAXVALUE 999999
  INCREMENT BY 1
  CACHE 10
  CYCLE;

SELECT id_seq.NEXTVAL FROM dual;

OUTPUT: 1

SELECT id_seq.CURRVAL FROM dual;

OUTPUT: 1

SQL Server:

CREATE SEQUENCE Test.id_Seq As decimal(3,0)  
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1 
  MAXVALUE 999999
  CACHE 10
  CYCLE
Go

SELECT NEXT VALUE FOR Test.DecSeq
Go

OUTPUT: 1

20. BLOB, CLOB

LOB Type	SQL Server Data Type	Max. Size
BLOB	varbinary(MAX)	2,147,483,647
CLOB	varchar(MAX)	2,147,483,647
CLOB – Unicode	nvarchar(MAX)	1,073,741,823

21. Meta data tables

Oracle	SQL Server	Meta data of
ALL_DIRECTORIES		All directories
ALL_OBJECTS, DBA_OBJECTS, USER_OBJECTS	sys.objects	All DB objects
ALL_PROCEDURES, DBA_PROCEDURES, USER_PROCEDURES	sys.procedures
ALL_SOURCE, DBA_SOURCE, USER_SOURCE	sys.sql_modules, INFORMATION_SCHEMA.ROUTINES	Source code of all stored programs
ALL_SYNONYMS, DBA_SYNONYMS, USER_SYNONYMS		All synonyms
ALL_TABLES, DBA_TABLES, USER_TABLES	sys.tables, INFORMATION_SCHEMA.TABLES	All Tables
ALL_VIEWS, DBA_VIEWS, USER_VIEWS	INFORMATION_SCHEMA.VIEWS	ALL views
ALL_USERS, DBA_USERS, USER_USERS		All Users
SEQUENCES	sys.sequences	All Sequences
COLUMNS	sys.columns INFORMATION_SCHEMA.COLUMNS	All Columns
INDEXES	sys.indexes, sys.index_columns, sys.foreign_keys, sys.foreign_key_columns, sys.partitions	All Indexes
TABLES	sys.tables, INFORMATION_SCHEMA.TABLES	All Tables
VIEWS	sys.views	All Views
ALL_CONSTRAINTS DBA_CONSTRAINTS USER_CONSTRAINTS	sys.check_constraints, sys.default_constraints, sys.key_constraints, sys.foreign_keys, INFORMATION_SCHEMA.TABLE_CONSTRAINTS	All constraints

22. ROWTYPE

Oracle:

Declare
  Cursor c1 is
    Select emp_id, emp_name
      from EMP;

  Rec EMP%RowType;
Begin
  Open c1;
  Fetch c1 into rec;
  Close c1;

  Dbms_output.put_line(rec.emp_id);
  Dbms_output.put_line(rec.emp_name);
End;

SQL Server:

Begin
  DECLARE @emp_id int, @emp_name varchar(50)

  DECLARE c1 Cursor for
    Select emp_id, emp_name
      from EMP

  OPEN c1

  FETCH NEXT FROM c1
   INTO @emp_id, @emp_name

  CLOSE c1
  DEALLOCATE c1

  Print @emp_id
  Print @emp_name
End
Go

23. Exit, continue, break

Oracle:

BEGIN
  WHILE (select avg(siPercentageCharge + 1) 
           from table1) < 18

  LOOP
    UPDATE table1
       SET siPercentageCharge = siPercentageCharge + 1;

    If (SELECT MAX(siPercentageCharge) 
          from table1) >= 20
      Exit;
    End if;
  End loop;
END;

SQL Server:

BEGIN
  WHILE (select avg(siPercentageCharge +1 ) 
           from table1) < 18
  BEGIN
    UPDATE table1
       SET siPercentageCharge = siPercentageCharge + 1

    If (SELECT MAX(siPercentageCharge) 
          from table1) >= 20
      BREAK
    ELSE
      CONTINUE
  END
END
GO

24. Date Arithmetic

Oracle	SQL Server
ADD_MONTHS	DATEADD
DATE + / – Number	DATEADD
DATE1 – DATE2	DATEDIFF
MONTHS_BETWEEN	DATEDIFF
LAST_DAY	DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0) – 1

25. TypeCast using Cast, To_Date, To_Char, To_Number, Convert

Oracle:

SELECT '30-APR-2015' Str_Date,
       CAST('30-APR-2015' AS DATE) Date1,
       To_date('30-APRIL-2015', ‘DD-MON-YYYY’) Date2,
       41.522 Num1,    
       CAST(41.522 AS CHAR(10)) Str_Num,
       TO_NUMBER('41.522') Num2,
       CAST('41.522' as number(10,2)) Num3,
       To_Char(41.522) str_num2,
       To_char(sysdate, 'DD-MON-YYYY') str_date2
FROM dual;

SQL Server:

SELECT '30-APR-2015' Str_Date,
       CAST('30-APR-2015' AS DATE) Date1,
       CONVERT(NVARCHAR, '30-APRIL-2015', 0) Date2,
       41.522 Num1,   
       CAST(41.522 AS VARCHAR(10)) Str_Num,
       CAST('41.522' as DECIMAL(10,5)) Num2,
       CONVERT(DECIMAL(10,5), '41.522') Num3,
       CONVERT(varchar(10), 41.522) str_num2,
       CAST(getdate(), 'DD-MON-YYYY', 101) str_date2
go

26. Execute Immediate

Oracle:

DECLARE
  V_sql varchar2(1000);
  V_columnList varchar2(75);
  V_city varchar2(75);
Begin
  V_columnList := 'AddressID, AddressLine1, City';
  V_city := 'London';
  V_sqlCommand := 'SELECT '||v_columnList||
                  ' FROM Person_Address WHERE City = ‘||v_city;
  EXECUTE immediate v_sql;
End;

SQL Server:

Begin
  DECLARE @sqlCommand nvarchar(1000)
  DECLARE @columnList varchar(75)
  DECLARE @city varchar(75)

  SET @columnList = 'AddressID, AddressLine1, City'
  SET @city = 'London'
  SET @sqlCommand = 'SELECT ' + @columnList +
                    ' FROM Person.Address WHERE City = @city'

  EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', 
          @city = @city
End
Go

   Or

Begin
  DECLARE @sqlCommand varchar(1000)
  DECLARE @columnList varchar(75)
  DECLARE @city varchar(75)

  SET @columnList = 'AddressID, AddressLine1, City'
  SET @city = '''London'''
  SET @sqlCommand = 'SELECT ' + @columnList +
                    ' FROM Person.Address WHERE City = ' + @city

  EXEC (@sqlCommand)
End
Go

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.