2005-11-10

 

Why MS SQL Server is preferable to Oracle

Required background: RDBMS:1, ADO.NET:1

MS SQL Server (hereafter referred to as MSSQL) only runs on Windows, which means it shares the scalability limitations of Windows. It certainly isn't the best at utilizing the hardware resources at its disposal. Its bundled "Enterprise Manager" is mediocre at best (this may have improved in 2005, but it just came out and I haven't tried it). It doesn't even have an interactive SQL commandline tool.

Oracle runs on everything from Windows to Linux to Solaris. It scales from a single-processor workstation box to a cluster of multiprocessor Sun big iron servers. It doesn't really have bundled management tools, but there are lots of powerful third-party tools out there. It does have an interactive SQL commandline tool, and it squeezes performance out of hardware. It is astoundingly tunable, allowing a trained DBA to squeeze even greater performance out of it. So why would anyone prefer MSSQL to the powerhouse that is Oracle?

Of course, I'm deliberately neglecting the most important part: ease of use. What I mean by that is ease of use in development, rather than ease of deployment or administration. While it's valuable to be able to get the most out of hardware, and to be able to improve performance by adding hardware, it does no good if you don't have the applications to work with the data. As a database designer and developer who has worked with both Oracle and MSSQL, I can comfortably say that Oracle is a pain. As I said, I'm not talking about deployment or administration, I'm talking about design and development of DB-backed applications.

Consider this table (the relevant columns are .NET Framework, SQL Server, and Oracle). The MSSQL column types map pretty directly to the .NET types. Java or even C/C++ can be substituted for .NET, though it's unsurprising that MS designed .NET to integrate well with MSSQL. Oracle, on the other hand, doesn't even have a Boolean column type. Or an integer column type, actually, since with ODP.NET (Oracle's own ADO.NET driver) one must retrieve any numerical value (and INT is a synonym for NUMBER(10) in Oracle) as a .NET decimal.

From a DB designer's perspective, not having a Boolean column type is crippling. It is always preferable to have tables with column names and types that are as self-documenting as possible. Having to choose either NUMBER(1) or CHAR(1) to represent a Boolean value defeats that. First off, it's easy to choose inconsistently as enhancement and maintenance proceed through the years and through many hands. This choice can and should be documented somewhere along with other conventions of the system, but such external documents are often ignored or even lost. Second, what values represent true and false? Is it one and zero for a NUMBER(1)? Non-zero and zero? Positive and negative? Null and non-null (heaven forbid, and what if you actually need a null)? How about for CHAR(1)? 'Y' and 'N'? 'T' and 'F'? How about 'y' and 'n' or 't' and 'f'? '1' and '0'? Non-null and null (same issues as with NUMBER(1))? Sure, you document it and, ideally, wrap it behind a data access layer in code (which is what I did when I wound up working with an Oracle DB), but someone will eventually mess up ("I used the COUNT for the value in that stored procedure." "But we use positive and negative to represent true and false." "D'oh!") and cause obscure bugs that are difficult to track down.

From a developer's perspective, there's an important distinction between monetary or arbitrary precision values (decimal), integer values, and floating point values (double). Having these all represented in the database as NUMBER makes code harder to understand and, therefore, harder to enhance and maintain. Similar issues apply to the considerations about Booleans above.

I don't feel the need to cover integration of transactions with code (MSSQL has it, not sure if Oracle does), quality of errors messages (Oracle's are abysmal, MSSQL's are decent), or debugging integration (VS.NET has it with MSSQL, I don't know of any integration with Oracle). I also won't discuss the relative merits of PL/SQL (Oracle's procedural SQL extensions for stored procedures and such) and T-SQL (MSSQL's equivalent) other than to mention the ease with which one can return datasets (i.e. sets of rows just like an ordinary query) from a stored procedure in MSSQL and the hoops one must jump through to do the same in Oracle. (I've done it. It ain't pretty, and it requires still more of those externally documented conventions.) The lesson from all this is that MSSQL deserves a certain popularity due to its friendliness to developers. Likewise, Oracle's hostility to developers should give one pause before choosing it as part of a system solution.

Oracle's reputation for performance has kept it going for many years, but that same success has led them to concentrate their efforts on performance rather than on making it a better development platform. Java stored procedures are nice, sure, but .NET stored procedures are just as nice (supported in MSSQL 2005) and both .NET and Java have data types Oracle doesn't support. Of course, I would prefer to work with either DB2 or PostgreSQL over Oracle or MSSQL any day.


Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?