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? (another question, what is the differences between i and g version of Oracles?)
In addition, have anyone got any information about the next release of SQL Server (codename, new feature, road map,...etc)? I'm glad to hear anything about it :)
I don't know if this is fair, but it's my experience having worked with both:
Oracle seems to be somewhat more "powerful" with some nifty features that SQL Server hasn't quite caught up with yet.
SQL Server is MUCH easier to work with, and has much better integration with microsoft products (if you're doing .NET development that is).
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.
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.
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
I've worked with both SQL Server and Oracle on a variety of projects. My thoughts:
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).
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.
I'd say:
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.
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.