Oracle vs SQL Server


So it's been a while since I have written something for my blog. But today I was talking to a colleague about how my transition into my new job over the last 9 months has gone. I mentioned to him that the although adjusting to the culture and methods of working took some time one of the challenges for me has been on the technology side. At this point I should note that a lot this blog will be geeky and techie. In all my previous development roles I have been using Microsoft Sql Server as the database backend and feel confident in writing very complex database scripts and looking under the bonnet when issues occur. However, in my present role we use Oracle as the database. Oracle's flagship product is established as probably the leader in enterprise size databases and although I have used it in the past – I was surprised how much difference there really was. I have still a lot to learn about Oracle itself and the PL/SQL language that sits on top of it. A lot of the differences surprised me and I thought I would try and write up some of the differences here and my thoughts on them.

       1)     Semi-colons. The first thing that will strike any developer is having to close off your SQL statements properly by using a ";" or "\". Whilst is doesn't sound too big of deal, I learnt the hard way about this when I was debugging a huge Stored Procedure I wrote in Oracle and I couldn't work out why I was getting unexpected result in testing. I hadn't closed off one statement properly and it caused an error which stopped the process running, half way through manipulating the data. In Sql Server you don't close off your statements.
        2)      Booleans -The lack of true/false, Boolean or bit type. In Sql Server you can create a column and specify its type as a bit field where 1 represents true and 0 represents false. This works out of the box and is straight forward. Oracle does not provide this at all. I found this amazing. Instead developers define a column as a number and then have to write a check constraint into the table definition that will only allow 0 or 1 into the column. We are now using Oracle 11g, which suggests in the 20 years or so Oracle has been around this has not been addressed. Wow !
        3)      Cursors! Cursors in both database are basically a pointer used to fetch rows into or from a result set In Sql Server, one of the ways to perform a loop in a piece of code is to use cursors. Unfortunately, cursors are generally recognised as extremely performance heavy and should be avoided. There are other ways to achieve loops in Sql Server and cursors are almost always bad! In Oracle Cursors are pretty much the only way to return data from a Stored Procedure and as such, do not have the same performance issues Sql Server faces. So I have had to reverse my deep desire to avoid cursors !
        4)      Schemas. In Oracle a database can have several schemas which house various database objects. This makes it easier to permission users or groups on only objects they care about. It also means that when querying a database, you have to fully qualify the object by specifying the schema e.g. "MySchema.MyTable". In theory SQL Server has the same but unless otherwise specified everything belongs to the "dbo" schema and when querying a Sql Server database you do not have to fully qualify it.
        5)      Packages. The code for a stored procedure are housed in what is known as a "package". This means you can group the code for similar or related stored procedures together and even have Package scoped variables. Similar to schemas, you then have to fully the qualify the SP name by doing <schema_name>.<package_name>.<stored_procedure_name>. Sql Server does not have this ability. I like the grouping of related Stored Procedures but I have yet to find a need for Package scoped variables and this to me sounds like a debugging nightmare.
        6)      Transaction Control. In oracle EVERYTHING is a transaction and is not permanent until it has been committed. While Sql Server has this ability it is not default and the developer or DBA has to specify transactional control.
        7)      Dirty Reads. This means that in SQL Server I can look at the data in tables that is being manipulated by someone or something else (using the "nolock" keyword). The data I look at could be inconsistent with what is being changed/updated/inserted/deleted. I.e. it is dirty. This may sound like a problem but really isn't in practice. As long as you are aware of this when designing enterprise level databases you shouldn't encounter issues. That is not to say I have never seen issues with this. Obviously this is configurable but rarely is. In Oracle I don't think this is possible. On a real time system, you sometimes want to see data being moved through the databases (uncomitted) to see what is going on, I think I like the option to read dirty data.
        8)      %Types (wildcard types). Normally in code, developers have to specify the data type for an object. In oracle you can use %Type which means the types can change or be different each time the code is run depending on conditions. While it sounds great, it also makes debugging a little harder.  To my knowledge, this is not possible in Sql Server.
        9)      Primary Key definitions. In Sql Server you can define a primary key by something like "Int Identity Not Null Primary Key" but it's not so simple in Oracle – you have to use the full syntax (also available in SQL Server btw) but that little bit easier in SQL Server
         10)   SELECT 1 +1 FROM DUAL. That statement in oracle will return 2. To the same in Sql Server I would write "SELECT 1+1". Notice the lack of a "FROM" part in the statement. What I am getting at is that I have to add a FROM dual if I doing a basic calculation, string manipulation or something similar. Granted it's not massive but I keep forgetting to do it and when I run my code I always get errors that I need to fix
        11)   NULLs and "". In Sql Server a string column (e.g. varchar) can be null or a have an empty value. By that I mean, the value could be NULL or could be "". In oracle "" is actually NULL. So if the column is defined to not accept NULL values, inserting a "" actually causes an error.
        12)   The MINUS operator. If I wanted to query a table in SQL Server and find data and filter out other data I can use nested loops, the NOT IN or NOT EXISTS operator. The same is true in Oracle but there is another way – the MINUS operator. I am yet to determine if this is a faster way to query the data but it is certainly quicker to write queries. A nice little touch..
        13)   TOP. If I wanted the first 100 rows of a table in SQL Server. I simply to SELECT TOP 100 * FROM xyz. The top function doesn't exist in Oracle instead. I have to write SELECT *FROM xyz WHERE ROWNUM<101. That little bit more to write.
        14)   CROSS APPLY. A very neat trick to do joins on tables or objects that don't have a natural JOIN in Sql Server. Hard to explain but really useful where you are creating objects in Stored Procedures for data manipulation and the data keeps evolving. To my knowledge there is no equivalent in Oracle.
        15)   Sql Server Profiler. An excellent tool which anyone with db access on SQL Server can use to see the CRUD statements firing on the server and how the engine itself is getting the data. Really useful to determine how the indexes are or aren't being used. There is no way to do this in Oracle. Which means a lot of statistical analysis is required on execution plans and autostatistics. This takes far, far longer.

I am sure there are plenty more differences and a quick google can show how much opinions range on the SQL Server vs Oracle debate. Given I have worked with SQL Server for 9 years and Oracle for less than 1 year…..I am naturally leaning towards SQL Server. Having said that, I really do like the SQL Server management studio user interface and the SQL Server profiler is also a major reason for my opinion. It also seems to me that to do the same tasks in Oracle as in Sql Server you have to write or specify that little bit more. Perhaps that means there is more flexibility in Oracle up front but I believe you can do the same in SQL Server if you wanted. 

Sorry for the geekyness of this blog! I am sure many of you stopped reading a while ago!  

Comments

Post a Comment

Popular posts from this blog

Let's have a meeting

I am zapped. I am drained both emotionally and physically. I love a good meeting like the next person but spending half my working day in them has killed me. I've been having a good 4 -5 hours a day of meetings recently. I wish I could say they were all useful and worthwhile, but when we had the meeting to discuss the next series of 6 meetings come up, I nearly lost the will to live. But, I pushed on through. I kept reminding myself about the silver lining and that there IS a light. However, today I wanted to take a pair of very blunt scissors and hurt someone, badly, repeatedly, drawing a lot of blood and making them drink it and then stabbing them again. After completing the series of six 2-hour long meetings, I got invite to a "group discussion" so that we can understand what came out of the meetings. Seriously, wtf?! I thought it was only the public sector that wanted a meeting to discuss the meetings that was arranged in another meeting???
Read more

What Makes a good autobiography

“It was as I was stumbling home at 5am, alone, tired and very, very drunk that it dawned on just how funny licking the dirt off the floor in the bar, actually was. “ Was that any good? I doubt it. What was wrong with it? It offers nothing out of the ordinary, nothing that differs from most young people in the UK. There is nothing says to the reader, I, Richard Jacobs, have lived a different and varied life such that others really want to invest a month of the spare time reading about it. But what if that was written by a premiership footballer reminiscing about a team Christmas party? I bet that quote would be all over sky sports news like an alcoholic at a free bar. So what does make a good autobiography? What makes a page turning, can’t put down, can’t go to sleep yet, introspective tale of person’s life. For me, what makes people first pick up an autobiography, read it cover to cover and then retell the stories and insights they have learnt to their friends is that th...
Read more

Charity

In a little under one week, I, along with around 35,000 others, will be competing in the London Marathon. Since the first London Marathon in 1981, half a billion pounds has been raised for charity. This is a phenomenal amount, and proof, that when it comes down it, people can display extraordinary acts of kindness and generosity. Yet for a time I couldn't  help but wonder why it takes such a grueling and painful effort from the tens of thousands of people each year to generate such revenues for charity. I’d like to note at this point, that I have been blown away by the support of friends, colleagues & family when trying to raise the sponsorship needed for the marathon. You would always like to believe that the people around would give while you put yourself through the painful months of training but I have truly been in awe of the level of generosity that people have shown and how far they have dug into their pockets. The point, I was getting at though, was that it has taken so...
Read more