MICROSOFT SQL SERVER VS ORACLE: A COMPARISON OF POPULAR RDBMS

1
Welcome To Loginworks Software
2
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.

3
SQL SERVER VS ORACLE TOP N ROWS

• I thought itd be nice to show how different
  these two are for retrieving the top number of
  rows.
• SQL SERVER
• 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

4

SQL SERVER VS ORACLE PAGINATION

Top seems easy enough to use and its not the
part of the ANSI standard for SQL. TOP is not
used in Oracles language instead, theres
ROWNUM.

If youre coming from SQL Server, ROWNUM doesnt
work as you may expect. You cant 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
lt 30

OR for a sorted list

SELECT table_nm FROM (SELECT table_nm FROM
sys.all_tables ORDER BY table_nm ) WHERE rownum
lt 30

Simple isnt always better!

We can use SQL Servers TOP operator but its
only going to give you the top number or percent.
To get the next 5 or next n youll 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

5
ORACLE



Paging in Oracle uses the ROWNUM function. ROWNUM
is also used for other tasks like getting the top
n row. Lets 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 lt 20) WHERE rnum
gt 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 youre using both have the same outcome!






6
Paging is a really important feature for web
pages and applications. Without it, youd 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 youre not returning
gigs and gigs of data to the web server to sort
through.

SQL SERVER

SQL server 2012 introduced OFFSET for pagination.
Lets 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.
7
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()

8
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 its a date.

CONVERT() is straightforward but theres a catch.
Let me make it sound more clear through an
example

Lets 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 youre stuck
parsing this and passing the values to one of the
FROMPARTS functions to build your date. This is
where Oracle shines.
9
ORACLE

The TO_DATE() function in Oracle allows a date
format to be passed. BY TO_DATE() function making
date conversion really easy. Lets 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
dualResult

08-JUL-10

Clearly, youd 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.
10

SQL SERVER VS ORACLE HOW TO REPLACE NULL VALUES?

• NULL Values can sometimes be a real pain. Dont
  worry though theres 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.

11
SQL SERVER

SQL Servers 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
12
Result

ORACLE

Oracles 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.
13
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 its not generally simple to see that same
functionality exists however, for the most part,
its conceivable to accomplish a similar
functionality on each database platform.
14
Thanku For Watching

Connect with This For More Inf

https//www.loginworks.com

Call us 1-434-608-0184