From Oracle to SQL Server

Published on 18 August 2016

I went from full Oracle to SQL Server and it is SO different…ok not that much. 🙂 But still I needed to found out how to do stuff with TSQL.

I guess there is ton of similar posts out there refrencing the differences between various DBMS qnd their languages. Nonetheless here is a few correpsondances, with use cases, between Oracle and SQL Server

  • Compute an operation through a simple select

  • PL/SQL:

SELECT 1+1 FROM dual;
  • TSQL :
SELECT 1+1
  • Replace a null value

NB:

To do an equivalent to Oracle’s NVL2 a CASE statement is necessary (putting mutliple values in COALESCE will only select the first non null).

  • PL/SQL:
SELECT NVL(LastName,'Doe') FROM Employee;
SELECT NVL2(workedHours, workedHours*hourlyRate, 0) FROM Work;
  • TSQL :
SELECT COALESCE(FirstName,MiddleName,'N/A') FROM Employee
  • Select only N lines

  • PL/SQL:

SELECT * FROM Employee WHERE rownum <= 100
  • TSQL :
SELECT TOP(100) * FROM Employee
  • Select parent/children hierarchy from a table

  • PL/SQL (cf. Hierarchical queries): use of CONNECT BY PRIOR

  • TSQL : use Common Table Expression (aka CTE) via the WITH key word:

;WITH CTE_Hierarchy AS (
  SELECT 0 AS depth
    ,e.EmployeeId
    ,e.Name
    ,e.ManagerId
  FROM test.Employee e
  UNION ALL 
  SELECT h.depth + 1 AS depth
    ,e2.EmployeeId
    ,e2.Name
    ,e2.ManagerId
  FROM test.Employee e2
  INNER JOIN CTE_Hierarchy h ON h.EmployeeId = e2.ManagerId
)
SELECT * FROM CTE_Hierarchy

There are still a few differences such as cursor browsing, no « FOR … IN … » syntax equivalent for SQL Server =(

Etc.  🙂

comments powered by Disqus