As I've been tasked with giving some SQL Server training at work, it was topical for me to compare SQL Server and Oracle. There are some DBA's who love Oracle and hate SQL Server and there those who love Oracle but hate SQL Server and few people imbetween. Personally, there are times when I want to do something with SQL Server and would love functionality that is only available in Oracle and conversely there are times when I want to do something in Oracle and I'd like something only available in SQL Server. So based on my experience here is a comparison of the database offerings from the Oracle and Microsoft which is by no means exhaustive, with SQL Server I like :-
- Ease of installation, although with 9i and more so with 10g Oracle has caught up, however there is no concept of having to manage multiple homes on SQL Server as there is in Oracle.
- Simplicity of creating temporary tables in transact SQL.
- Ease with which tables can be populated with the output of stored procedures, with Oracle you need to mess around with user defined types.
- Low storage administration overhead, out of the box all storage management is bit map managed, Oracle did not get this until automatic segment management came along in 9i. Also you by default you do not have to worry about tablespaces or storage clauses. Interesting, Dave Ensor (ubiquotous of the UKOUG circuit) used to mention that he would create all objects in the same tablespace bin some of his databases to disprove myths about fragmentation and the number of extents used by a segment having any affect on performance. This is akin to what you have in SQL Server, each database is stored in one or more datafiles. Ok, so with 10g you get ASM, but with SQL Server there are no undo tablespaces to worry about nor any temporary tablespaces as you use tempdb which comes almost out of the box.
- Self managing memory, Oracle started down this road with pga_aggregate_target in 9i and the SGA management is simplified in 10g with the introduction of the sga_target parameter, with SQL Server you simply specify one
memory foot print. - Data Transformation Services, this is a free, powerful and easy to use ETL tool that comes
with SQL Server, in fact some people like it so much that they advocate getting SQL Server, using DTS and chucking away the rest if SQL Server is not your database of choice.
I don't like :-
- Basic elements of the SQL language are missing from SQL Server 2000, like the MINUS operator.
- Unwieldy date manipulation, try achieving the same affect as a TRUNC on a date type in SQL Server
- Lack of tuning tools and utilities, e.g. nothing to compare to the Oracle wait interface no facility to trace individual sessions unless you incorporate code in stored procedures to invoke query profiler, however this is a bit long winded compared to the means available to you in Oracle.
- SQL Server enterprise manager is primitive compared to out of the box web enabled console you get with 10g.
- Lack of an interface into the inner workings of the instance, Ok with SQL Server 2005 we do finally get "dynamic management views" but these are not as mature as Oracles v$ views and fixed tables. Generating execution plans on queries against v$ views can give great insights into how Oracle works under the covers and the likes of Jonathan Lewis and Guy Harrison have written papers on using these fixed views directly and tuning queries running against these v$ and x$ objects.
The last word could go down to platform support, in that every ten years the popular server platform shifts; in the seventies the mainframe reigned supreme, then UNIX came along in the eighties and Windows in the nineties and it now appears Linux is comming to power which would leave SQL Server high and dry.
