In this article we'll explore the changes to several popular databases and compare what they have now and what they hold in the near future.
We will explore 3 databases: Microsoft SQL Server 2000->2005, MySQL 4.0 -> 5.0, and PostGreSQL 7.4->8.0->8.1.
For each database, we'll outline the key features, and what will be in the future additions. In the final part, we will compare side by side key features that we look for in a database management system and compare how each stacks up.
+ denotes not a new feature, * denotes new feature
in this version, - nonexistent ranking 0-3 (0 nonexistent, 1 fair, 2 good, 3 excellent) .
Microsoft SQL Server
Microsoft SQL Server current edition is SQL Server 2000 Standard (note SQL Server 2005 Express is also now available, but the SQL Server 2005 Standard->Enterprise are still in beta), which has been out for a couple of years. In this year, they will be releasing the 2005 version which will have major enhancements. We'll outline the major features below.
Feature |
SQL Server 2000 |
SQL Server 2005 |
Stored Functions |
* but only non-volatile functions (1) |
+ (2) |
CROSS APPLY predicate |
- |
* - allows you to join functions that return tables and have changing results in a from clause |
Language support |
only T-SQL (1) |
Any .NET language (2) |
Stored Procedures |
+ (2) |
+ (2) |
Subselects |
+ both static and correlated (2) |
+ both static and correlated (2) |
Native Cross-tab queries |
|
* (3) |
Updateable Views |
+ (automatic single table views, instead of triggers to update non-updateable views) |
+ |
Indexed Views |
* (only the enterprise version) |
+ |
Triggers |
+ (row-level) |
+ (row-level) |
Index based on a calculated field |
+ (row-level) |
+ (row-level) |
OS Support |
only Windows (1) |
only Windows (1) |
Top/Limit queries |
+ (2) |
+ (2) |
|
Rules |
0 |
0 |
Cost |
Closed Source, free MSDE. Full version costs money. |
Closed Source, SQL Express free, other versions such as Standard, Enterprise, Workgroup cost money |
The new version of SQL Server, SQL Server 2005 (called Yukon) will have major enhancements. The main ones are listed above. Perhaps the most powerful are the introduction of the PIVOT, UNPIVOT which convert a table to a cross tab query and convert a cross tab to standard tabular format, and
CROSS APPLY and OUTER APPLY predicates which allow one to join correlated functions that return tables.
The new version will have native support for .NET languages as well (such as VB and C#).
MySQL
MySQL is perhaps the most popular open source database around. What is most appealing about it is possibly the ease with which it can be installed and its support for numerous OSes.
In prior versions, MySQL lacked many options that a black-belt DB programmer or DBA would find useful. It is now beginning to make up for this in version MySQL 4.0 (recently released), and the upcoming MySQL 5.
Feature |
MySQL 4 |
MySQL 5 |
Stored Functions (User-Defined Functios) |
- |
- |
Language support |
only SQL and static bindings to c libraries |
only SQL and static bindings to c libraries |
Stored Procedures |
- |
* |
Subselects |
* |
+ (2) |
Views |
- |
* |
Triggers |
- |
* |
Index based on a calculated field |
+ (row-level) |
+ (row-level) |
OS Support |
Windows and Most Unix |
Windows and Most Unix |
Cost |
Open Source, but commercial license purchase required for non-GPL projects (2) |
Open Source, but commercial license purchase (2) |
PostGreSQL
PostGreSQL is perhaps the feature-rich open source database around. What is most appealing about it is its relative ease of setup, advanced ANSI-SQL and other Object Relational features, its support for numerous OSes, and its generous licensing model.
In prior versions (7.4 and below), PostGreSQL lacked native Windows support which was a big disadvantage when compared with databases such as MySQL. PostGreSQL 8.0 and above now sport native Windows support and a very simple to use Windows installer.
Feature |
PostgreSQL 7.4+ |
PostgreSQL 8.0 |
Point-In-Time Recovery |
- |
* |
Tablespaces |
- (simulated on Unix using symlinks) |
* (allow breaking up tables into several drives, file systems) |
Language support |
SQL, PgSQL, Perl, static bindings to c libraries |
SQL, PgSQL, improvements to Perl, beta .NET, and static bindings to c libraries |
Stored Procedures |
- |
8.0 still missing, 8.1 will have Stored Procedures. Note that stored functions can be used in lieu of Stored Procedures in almost all cases. |
Save Points |
- |
* allows for partial commit of a transaction |
Views |
+ |
+ |
Triggers |
+ |
+ |
Index based on a calculated field |
+ |
+ |
OS Support |
Windows (via Cygwin) and Most Unix |
Windows (native) , Most Unix, Mac OSX |
Cost |
Open Source, Free for all uses BSD License (3) |
Open Source, Free for all uses BSD License (3), Commercial versions such as Pervasive, Mammoth, Enterprise Db that have added features on top of the standard PostGreSQL offering |
Articles of Interest |
Postgres OnLine Journal | Site dedicated to providing in-depth information and examples about PostgreSQL. Check out the monthly periodical. |
Using CROSS APPLY in SQL Server 2005 | Gives examples of how to use the new CROSS APPLY clause in SQL Server 2005 that lets you join tables with changing table valued functions |
Open Source Relational Databases and the Enterprise | A white paper that summarizes use of open-source databases in the enterprise as well as a summary of the strengths and weakness of each stacked up against Oracle, SQL Server, DB2 |
EnterpriseDB |
A commercial version of PostGreSQL that adds on a lot of ORACLE specific features. Enterprise DB has the ability to easily replace Oracle Database in most applications with very little code change. |