SQL Server VS Oracle

Question

I'm studying SQL Server but don't know much about Oracle. Can anyone give me a fair comparison of them? I've read a comparison about SQL Server 2000 vs Oracle 9i, but it's a little out of date. Have SQL server 2008 caught up Oracle 11g/i yet?

Answer 1

Just my $0.02 after working with SqlServer for years and Oracle for 4 months now.

SqlServer is much easier to use, this allows you to concentrate on what you want the database to do (solve some business problems) instead of wrestling the server all the time.

Because Oracle makes you configure so many settings, there are a lot of ways to get them wrong. This leads to endless discussions about partitioning, the type of index to use, etc etc. During those discussions, nobody is thinking about the business problems.

An analogy: I once had a olympus camera with a good lens and some simple settings. I made nice pictures with it so I upgraded to a DSLR with all kinds of knobs and dials. With that my pictures weren't half as good because I was concentrating on the camera instead of the subject.

That to me is the difference between Sql and Oracle. Sql is easier to work with and therefore gets more things done. Ideas go from the whiteboard to working code so much faster. Only when you push for very large databases that Oracle might take the edge but I'm not even sure of that. I don't think there's something that Oracle could handle that SQL couldn't.

Edit: Also, Oracle is very poor at in-database analytics on large data volumes because of context switching to PL/SQL. So if you have a few complex functions you want to run on your data, don't use Oracle. MS SQL on the other hand shines in this area because it does not suffer from this issue and also has .Net integration.

Answer 2

I'd say:

• Oracle has a much better procedural language. T-SQL feels only half finished compared to PL/SQL.
• SQL Server has a much better optimizer. When I used Oracle, I had to hint every query to make sure that it didn't take forever to finish, SQL Server just works. Might have been that the server was badly configured, though, which takes us to the next point
• SQL server is a lot easier to work with. You need dedicated DBAs to administer Oracle, but pretty much any bozo can keep SQL server running.
• Oracle has a better, more predictable and more well-documented concurrency model.
• Oracle's documentation is superior in most ways.
• SQL server integrates better with .NET (like the devil Linq2SQL).
• If you use Oracle you also have to buy TOAD, but the included Manegement Studio will do for SQL Server.
• Oracle has more cool but hardly necessary features like extensible indexing.

If I were to choose I'd go for SQL server because of the better optimizer. However, I don't really feel safe when multiple people use the same rows in SQL server.

Answer 3

I don't know if this is fair, but it's my experience having worked with both:

1. Oracle seems to be somewhat more "powerful" with some nifty features that SQL Server hasn't quite caught up with yet.

2. SQL Server is MUCH easier to work with, and has much better integration with microsoft products (if you're doing .NET development that is).

Answer 4

Ultimately theres not a whole lot of difference now. There is very little one can do that the other can't but the out-of-the-box configs tend to differ somewhat.

The most obvious difference is that Oracle uses SEQUENCEs and SQL Server uses auto-increment columns. There are pros and cons to each. Some prefer one over the other.

From my experience doing Top N queries in SQL Server seems a little easier than Oracle.

Out of the box Oracle uses MVCC (uncommitted data is non-blocking to reads by other transactions--the database maintains a consistent view). SQL Server I only discovered recently has this too but it's not the default option and is called something quite different. Transaction isolation level or something.

PL/SQL and T-SQL are reasonably different in syntax in some areas but the concepts are mostly the same.

Personally I found SQL Server management Studio to be OK but horrendous in some areas, like its data import/export just didn't work with auto increment primary keys. This was a known bug going back years on SQL Server 2005. I don't know if they've fixed it in 2008 or not. With PL/SQL Developer (doesn't ship as part of Oracle; it's a separate commercial product) imports and exports were a breeze.

Oracle has an annoying limit on VARCHAR(2) columsn: limited to 4k in length. They're much longer in SQL Server (64k?).

I found the SQL Server security model just bizarre. TCP was disabled by default (Windows authentication only) and then you had to setup a logon and a user? I never quite got that. It just seemed to be to be overly complicated whereas in Oracle its just:

CREATE USER blah IDENTIFIED BY password;
GRANT CONNECT TO blah;

Done (although granting some other privileges/roles like RESOURCE is pretty common).

I never quite figured out the backup/restore from the managemen tstudio. Recover would fail saying someone was connected to the database but that someone was me attempting to run the recover.

And before anyone has a go at me for the above, I realize fully well that many of them stem from my lack of SQL Server experience but honestly theres still a level of consistency (with the abstraction) and portability of concepts that is expected.

Answer 5

I am a software developer. I primarily write software on top of databases (or help my team do so, at least).

This is what I can say about Oracle: Every time I want to do something, my past experience with it is not helpful in understanding how to do my current task. I have spent a lot of time searching for some kind of useful explanation of a cryptic error message only to find a few posts suggesting that the OP contact Oracle support. I just discovered that their data pump import tool gives errors (which were rather cryptic) if you have a NOT NULL constraint on a spatial column in Oracle 10g. Even after removing the constraint, I'm having a problem where the import hangs doing nothing. Speaking of data pump, it only works locally. The import/export tools that work from remote machines are deprecated. When you have an error in your procedure, it tells you it failed to compile but creates the procedure object in an unusable state anyway. Then you have to run a command to get the errors. The user and permissions system is poor. A user is a schema in Oracle, but for all the vast number of permissions, there is no permission to allow a user to access all objects on a schema and allowing a user to kill a session requires granting the ALTER DATABASE permission (which gives the user probably far more power than you want). For spatial junkies, it can't parse the WKTs it generates for SRID 3857 (it spits out numbers of the form 0.00000e6 when the coordinates become large enough and can't parse that numeric syntax). Yes, a lot of these are specific examples, but running into these kinds of issues where I can't just do what I want to do is a daily experience for me, with multiple issues coming up during a single task.

I've only had limited experience with SQL Server, but what I have done has not been nearly so rough on me. I was able to figure out things with far more ease, and I don't recall seeing error messages that don't help me figure out what my problems are. My coworkers who have worked more extensively with SQL Server have reported a similar experience.

Maybe it's just me, but working with Oracle seems to constantly be a painful, tedious process of hunting down strange errors and trying to work around the fact that functionality I need is missing and left for me to implement myself (like auto-incrementing keys). My bottom line opinion is this: maybe Oracle can perform better, but only if you have the time and money to make Oracle work and then figure out how to tune it and the money to get your developers to learn, too. Oracle is just plain hard to learn and use.

Answer 6

I'm a SQLServer person, and know very little about Oracle, but in my view SQLServer vs. Oracle is a religious argument. Might as well ask which is better Windows, Mac or Linux.

Both SQLServer and Oracle are enterprise capable databases with spectacular developer, operations and IT support stories. Both are capable of handling any dataset you care to throw at it when configured and sized correctly and both are capable of coming to a screaming halt when poorly configured and sized.

As for "ease of use", as I said, I'm a SQLServer guy and I find SQLServer really easy to use (admin). Of course, I'd likely find Oracle to be a pain because I think in the SQLServer way. Likewise, I'm sure the Oracle admins/dev find Oracle easy to use because they think Oracle and SQLServer is strange to them.

I think the one true difference between then is that Oracle is cross platform while SQLServer obviously isn't. However, I don't see that as a big deal as I'm not looking to replace Windows with Linux.

Answer 7

I've worked with both SQL Server and Oracle on a variety of projects. My thoughts:

• SQL Server works better with .NET
• Generally, SQL Server is easier to install and work with. As a developer, I've rarely needed a DBA for SQL Server, but always seem to run into something with Oracle that I need a DBA to do.
• I greatly preferred PL/SQL (Oracle) over T-SQL (SQL Server) for writing stored procedures
• Oracle seems to have more significant hardware requirements
• The default admin tools for SQL Server are easier to use than the ones included with Oracle
• Development shops rarely mix the two

Answer 8

As cletus mentioned, Oracle has a fundamentally different locking structure to SQL Server: "writers don't block readers and readers don't block writers" as Tom Kyte (his site is a fantastic free resource) is fond of saying. Something similar was built in to SQL Server 2005, but it was done such that row versioning was implemented in tempdb, which doesn't sound particularly scalable. And it's turned off by default, so the folks at Microsoft don't seem to be particularly convinced by it.

Oracle also has a very different caching structure for queries, so that using bound variables, or placeholders, can make a huge difference: the "hard" parsing (i.e. the database checking the SQL syntax as if it is the first time it has seen it) can be kept to a minimum, meaning only "soft" parsing (filling in the variables) takes up overhead. From 10g onwards there is also the provision of bind "peeking" so that you can use bind variables without being at the mercy of skewed data.

Plus there are things like a really efficient and streamlined fulltext search capability, and row level versioning.

Answer 9

The latest versions of SQL Server and Oracle are both Enterprise ready, and are largely similiar in what they can do.

They obviously don't work in the same way, but by and large what you can achieve in both are the roughly equivalent in performance and functionality.

If you want lots of references to articles do a search on google: SQL vrs Oracle

Having said that i'm sure there are differences in licencing and support agreements.

For me personally, we have Oracle and SQL Server, where I've worked in the past, and comparing the two I can't see anything in Oracle which I couldn't do in SQL Server (One of my roles was the SQL Server DBA.).
I'm sure the Oracle DBA felt the same. We had 10's of SQL Server's and databases, and the databases ranged from tiny, to hundreds of GB's. Oracle had multiple servers and databases and they were large too.

In my mind this has come down to a managerial/cost issue, rather then a technical/functional issue it used to be, with the addendum that certain applications are more suited to one database over another.

My best guess on the meaning of i and g (without looking them up).
Would be, i is internet ready, g is grid enabed.

Here's some information about SQL Server 2010: Microsoft lays out SQL Server road map

Here's Microsoft's comparison against Oracle: More Performance | More Scalable | More Value

Here's Oracle's comparison against Microsoft: Oracle(r) Database 10g Release 2 Outperforms Microsoft SQL Server 2005 in Head-to-Head Comparison

Answer 10

The advantage of Oracle is the tuning aspect, in Oracle you can pretty much change everything... I don't know if you get that level of customization in SQL Server.

On the tools side SQL Server shines, its very easy to work with (has limitations but still very easy).

Integration... well it depends, if you're using .NET then SQL Server (even though you can use Oracle with it too) if you're using Java then Oracle (you can also connect to SQL Server using Java).

If you ask me which one to choose, I'll say NONE!, since you get pretty much the same quality and advanced technology with free database servers like MySQL or PostgreSQL (and since I use Python/Django and .NET they integrate pretty well).

Which one should you pick? I don't know, try both!, it won't take you more than a day to install and play with them :). Both have advantages and both are cool, we've reach the point that it doesn't matter which DBMS you pick (the 4 big ones, MySQL, PgSQL, MSSQL or Oracle).

Answer 11

I believe there is a long list of features that Oracle has which SQL Server does not. That doesn't that mean you can't accomplish the same task in MSSS, no but these features do mean that Oracle can be faster for the same thing

Table Clusters.

We can store data from more than one table on the same block/page. Take for example the system tables that contain tables, columns, indexes, and constraints. If you don't have table clusters, in order to extract a table, all its columns, indexes and constraints, you'd query the parent table's name index, then go to the parent table, read the page/block with that row. Then get the PK, take that value into the index on the FK on column table, get the rowid, go to the blocks/pages with those rows, repeat for the other two tables. Wheh... that's a lot of work.

In Oracle you can specify that those four tables will live on the same table cluster all based on the Table_PK. So the read would look like. Check the cluster index for location of that table and grab all 4 tables' data with one or a couple block(s). Voila.

In both systems it's 4 tables, it would look exactly the same to an application but if minimizing read time were crucial Oracle could do it faster.

Answer 12

As an end-user (non IT), I have a propensity (and bias) towards Oracle. I've worked in Oracle for three years, and now have been dropped into the world of MSSS. Personally, I find the SQL Server environment less intuitive and very “buggy”. I don’t write code, but I do need to query large datasets, and find it (OSD) more scalable.