Microsoft SQL Server vs Oracle: A Comparison of Popular RDBMS

There are many different relational database management systems (RDBMS). You have probably heard about Microsoft Access, Oracle, Sybase and MySQL, but the two most popular and widely used are Oracle and SQL Server!

In this blog, I will provide the difference between SQL and Oracle. Both have many similarities, as well as many differences, which I hope will definitely help you sort out in determining which is the best for you to learn, or for your organization to opt for their use.

SQL Server vs Oracle: Top N rows

I thought it’d be nice to show how different these two are for retrieving the top number of rows.

SQL Server

There are two different ways to get the top rows in SQL Server. You can either do this by [n] or by [%]. You can choose to use an order by or not. If no ORDER BY clause is used the rows will be based on the tables default sort order.

SELECT TOP 10 CutomerId FROM Customer

OR

SELECT TOP 1 PERCENT CustomerId FROM Customer

Oracle

Top seems easy enough to use and it’s not the part of the ANSI standard for SQL. TOP is not used in Oracle’s language; instead, there’s ROWNUM.

If you’re coming from SQL Server, ROWNUM doesn’t work as you may expect. You can’t just replace TOP with ROWNUM. Another point is that ROWNUM is assigned before the ORDER BY so, you may want a derived table to get the top rows with a sort.

SELECT table_nm FROM sys.all_tables WHERE rownum <= 30;

OR for a sorted list

SELECT table_nm FROM (SELECT table_nm FROM sys.all_tables ORDER BY table_nm ) WHERE rownum <= 30;

Simple isn’t always better!

We can use SQL Server’s TOP operator but it’s only going to give you the top number or percent. To get the next 5 or next [n] you’ll have to use OFFSET clause that was introduced in SQL Server 2012. This is called Pagination and is a very important feature for web pages or applications that want to paginate results.

To find the next [n] in Oracle is a breeze with ROWNUM. To paginate with ROWNUM in Oracle you can simply use two tables like the following example:

SELECT table_nm FROM (SELECT table_nm , rownum rnum FROM (SELECT table_nm FROM sys.all_tables ORDER BY table_nm ) WHERE rownum <= 20) WHERE rnum > 10;

SQL Server vs Oracle: Pagination

Paging is a really important feature for web pages and applications. Without it, you’d be passing large amounts of data to the application and expecting the application code to figure out which rows it needed to display.

Thankfully, someone smart came up with a way to do this on the database so you’re not returning gigs and gigs of data to the web server to sort through.

SQL Server

SQL server 2012 introduced OFFSET for pagination. Let’s examine the code:

SELECT CustomerId, City, PostalCode

FROM Customer

ORDER BY CustomerId OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

An ORDER BY clause must be specified to use OFFSET. You can choose to Fetch next [n] or not. Specifying the FETCH NEXT [n] will limit the rows returned to [n] while using OFFSET without a FETCH NEXT will only omit the first [n] rows your offset by. In this case, 10 rows at the front would be ignored.

Oracle

Paging in Oracle uses the ROWNUM function. ROWNUM is also used for other tasks like getting the top [n] row. Let’s examine the code:

SELECT table_nm FROM (SELECT table_nm, rownum rnum FROM (SELECT table_nm FROM sys.all_tables ORDER BY table_nm) WHERE rownum <= 20) WHERE rnum > 10;

This can easily be used by an application to programmatically get the rows you want.

According to my opinion, the OFFSET FETCH feature of SQL Server is much easier to use. No matter which you’re using both have the same outcome!

SQL Server vs Oracle: Convert string to date

SQL Server

There are many ways to create a date from a string:

• CONVERT()
• CAST()
• DATEFROMPARTS()
• DATETIMEFROMPARTS()
• DATETIME2FROMPARTS()
• SMALLDATETIMEFROMPARTS()
• TIMEFROMPARTS()
• DATETIMEOFFSETFROMPARTS()

All the above-mentioned functions are used to create a date from a string and you also have the ISDATE() function which tests the value to be sure it’s a date.

CONVERT() is straightforward but there’s a catch. Let me make it sound more clear through an example…

Let’s say you want to convert a string of “MMDDYY” to date.

SELECT CONVERT(date, ‘080902‘)

Result:

2008-09-02

This is the result you get whether you use the format parameter or not. So now you’re stuck parsing this and passing the values to one of the FROMPARTS functions to build your date. This is where Oracle shines.

Oracle

The TO_DATE() function in Oracle allows a date format to be passed. BY TO_DATE() function making date conversion really easy. Let’s see it in action.

SELECT to_date(‘070902’, ‘mmddyy’) FROM dual;

Result:

09-JUL-02

In TO_DATE() function we can pass a date format the types of strings.So we can pass to the TO_DATE() functions are near endless. How about ‘‘HHMMIIDDSSYYYY’’?

SELECT to_date(‘02073908532010’, ‘mmddyy’) FROM dual;

Result:

08-JUL-10

Clearly, you’d be placed on some sort of medication if you were storing your dates in HHMMMIDDSSYYYY string format, but the greatness that comes out of this is that no matter the format you can convert to a date as long as you know the pattern.

SQL Server vs Oracle: How to replace NULL values?

NULL Values can sometimes be a real pain. Don’t worry though there’s a simple solution to handle this pain. For this, you can Simply replace the NULL value with another.

Comparing a column with NULL and replacing with another value is really simple. There are so many built-in functions for replacing NULL values.

SQL Server

SQL Server’s built-in function for replacing NULL values :

• ISNULL().
  

It is very simple to use! It takes two parameters and check the first for NULL and if NULL is found then substitutes the second parameter for the first parameter.

SELECT ProductID, Weight, ISNULL(Weight, 0.00)
FROM Production.Product

Result:

1, NULL, 0.00

2, NULL, 0.00

Another Example:

SELECT ISNULL(NULL, ‘XYZ’)

Result

XYZ

Oracle

Oracle’s built-in function for replacing NULL values :

• NVL function

The NVL function replaces an NA value or an empty string with a string.

Example:

SELECT NVL(city, 'N/A') FROM Customer;

The above query would return ‘n/a’ if city field contained a null value. Otherwise, it would return the city value.

NULLIF function in Oracle is completely different to SQL. NULLIF compares to values and if they are the same then it returns NULL.

Concluding Words

Composing the above has helped me understand that there is in fact not a great deal of difference between SQL Server and Oracle these days, as far as the functionality that they offer. They may utilize diverse terminology to portray certain pieces of functionality. Throughout the years as one supplier has presented another component the other supplier has actualized a comparable arrangement of usefulness into their product. The features have distinctive names in each product, so it’s not generally simple to see that same functionality exists; however, for the most part, it’s conceivable to accomplish a similar functionality on each database platform.

• About
• Latest Posts

Follow me!

Imran Saifi

Database Administrator at Loginworks Softwares

I am a database administrator who has the responsibility of maintaining the integrity and performance of company databases. I guarantee that the data is stored securely and ensure all the users are informed of any changes in the same. Apart from managing all this bulk of technological binaries, I love to enjoy little things in my life. I am an avid foodie who loves to explore the abundance of specialties our cultures offer. I am also into sports, especially cricket. I make sure that I never miss my weekend morning cricket matches alongside my long-time friends. I cherish both my professional and personal lives to the fullest.

Follow me!

Latest posts by Imran Saifi (see all)

• Difference Between SQL and MySQL - April 14, 2020
• How to work with Subquery in Data Mining - March 23, 2018
• How to use browser features of Javascript? - March 9, 2018