I’m an Oracle fanboy. When I say fanboy, I mean, if I had to pick my preferred relational database, it would be Oracle. This isn’t because I necessarily think it’s better, faster or easier to use, but rather because it was my first. I’m also lucky enough to have used other technologies and tool sets during my career so far. Microsoft SQL Server is the other primary tool in my day-to-day work life.
I like SQL Server. Initially, this was not the case, but that was because things I was so easily able to do using Oracle, I was unable to with SQL Server. Now that I have a few years of experience using both, I have found alternatives in each and there are features that I prefer in one over the other. This blog is about some of my personal favourite things about each, what one RDBMS does better than the other, and what exists in one, but not the other. This is based mainly on my experiences using SQL Developer and Toad for Oracle 10g and 11g and SQL Server Management Studio 2012 and 2014 for SQL Server 2012 and 2014. From this point forward I will simply refer to them as Oracle and SQL Server.
Create or Replace
First up is Oracle. Some of the simplest things which I took for granted before I used SQL Server are now some of my favourite things about Oracle. First and foremost, is the CREATE OR REPLACE option when creating or altering a view, function or procedure. In Oracle you can recreate your view, function or procedure by simply re-executing the code because it either creates it or replaces it if it already exists. Magic! To do the equivalent in SQL Server, you have to first drop the view if it exists and then, create it. Or alternatively, wrap the view in a procedure that checks to see if the view already exists. Yes, this makes me sound really lazy, but it is just such a simple feature that pleases me so much.
During those long tired days, where the chance of accidentally running a delete or update statement without a fully defined Where clause is so much higher, it’s nice being able to simply rollback the transaction in Oracle without a second thought. In SQL Server you have to explicitly specify a transaction. If you forget to do this, and then accidentally update the wrong piece of data, you’re going to have some work to correct its proper state. Of course, the flip side of this is forgetting to commit a transaction leaving a table locked from others while you’ve gone home early to enjoy the cricket. It is a nice safeguard to have.
Dates and Trunc
Dates in databases will always be an absolute pain to deal with. Matching dates across different formats, changing date formats, changing from a date to a string and back again, sometimes it can be excruciating. The Trunc function in Oracle helps when you want to just return the date without the extra time stamp information. It strips out all time information from a time stamp leaving just the date. This makes it much easier to date match when querying the data. The Trunc function can also be used with numbers, to manipulate the number of decimal placings or the rounding of the number. It can be a very handy feature during your ETL process!
Text Search and Instr
I recently had to test the difference between two reports, one written for SQL Server, the other for Oracle. The output was ideally meant to be the same. This report needed to text search and then return results based on the nth appearance of a particular string. Using the INSTR function, Oracle made this a relatively simple task. SQL Server was much more complicated and as a result the report outputs were not an exact match. In SQL Server you can easily find the position a particular string within a text field. However, with Oracle you can take it one step further and find subsequent occurrences of that string. So using Oracle you can find the position of the first, second etc.occurrence of a string. A very handy feature in what can be a very finicky task.
Multiple Results Returned
Now it’s SQL Servers turn.First up would probably be my favourite feature in both SQL Server and Oracle. The ability to run multiple queries in one window and having multiple result sets return all in one place. This makes comparing results so much easier, especially when testing and debugging! Whenever I use Oracle, this is what I miss most.
The identity column is an incredibly easy way to automatically sequence your key columns on a table. Oracle has recently caught up and integrated this feature into their 12c release. Before that, a sequence and a trigger had to be used to get a column to automatically increment a new ID whenever a row was inserted. So while Oracle may now do this, SQL Server takes the points for already doing it and for making my life easier first.
DDL Statements within a Procedure
In SQL Server, you can execute DDL statements, such as a TRUNCATE TABLE statement directly within your stored procedure. This feature also falls into the “He’s so lazy” category, because it doesn’t take much more to do the same in Oracle, you just have to enclose your DDL in an EXECURE IMMEDIATE statement. Once again SQL Server’s ability to have features that make life easier wins me over.
Table and Column Naming Character Limits
One of the most restricting things I found in Oracle was the 30 character limit on table and column names. SQL Server, thankfully, allows for 128 characters. Most of the time a table or column name won’t even be close to pushing the 30 characters in Oracle, but sometimes your data vault or dimensional model requires a table name of great length and then you end up having to abbreviate every word to get it to fit, but not with SQL Server. Oracle, 30 is not enough, especially if you want your table name to have some genuine meaning!
I could go on for countless more paragraphs picking different things I prefer in Oracle over SQL Server and vice versa, but I won’t. Some of these things may seem so simple or even lazy, but I bet half of you choose escalators over stairs, so I’m allowed to parade these small wins in my work life. Both Oracle and SQL Server have their advantages and disadvantages but, both get the job done at the end of the day. Sometimes things are easy, sometimes things are hard.