Back to Papers and Articles
Brief Summary of Popular Database Systems
Copyright 2006 Paragon Corporation   ( October 31, 2006)
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 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 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 JournalSite dedicated to providing in-depth information and examples about PostgreSQL. Check out the monthly periodical.
Using CROSS APPLY in SQL Server 2005Gives 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 EnterpriseA 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.

Back to Papers and Articles