Title: MICROSOFT SQL SERVER VS ORACLE: A COMPARISON OF POPULAR RDBMS
1 Welcome To Loginworks Software
2MICROSOFT 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.
3SQL 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
5ORACLE
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!
6Paging 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.
7SQL 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()
8All 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.
9ORACLE
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.
11SQL 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
12Result
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.
13CONCLUDING 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