Comparison of relational database management systems

Encyclopedia : C : CO : COM : Comparison of relational database management systems

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. This article is not all-inclusive or necessarily up-to-date. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

Contents

1 General information
2 Operating system support
3 Fundamental features
4 Tables and views
5 Indices
6 Other objects
7 Partitioning
8 See also
9 External links

General information

	Maintainer	First public release date	Latest stable version	Software license
Adaptive Server Anywhere	Sybase	1995	9.0	Proprietary
Adaptive Server Enterprise	Sybase	1987	15.0	Proprietary
ADS	ADS	2003	3.4	Proprietary
Apache Derby	Apache	2004	10.1.3.1	Apache License
DB2	IBM	1982	8.2	Proprietary
Firebird	Firebird Foundation	July 25, 2000	1.5.3	Initial Developer's Public License
Informix	IBM	1985	10.0	Proprietary
HSQLDB	HSQL Development Group	2001	1.8.0	BSD
H2	H2 Software	2005	0.9	Freeware
Ingres	Ingres Corp.	1974	Ingres 2006 II 9.0.4	GPL and proprietary
InterBase	Borland	1985	7.5.1	Proprietary
MaxDB	MySQL AB, SAP AG	?	7.6	GPL or proprietary
Microsoft SQL Server	Microsoft	1989	9.00.2047 (2005 SP1)	Proprietary
MySQL	MySQL AB	November 1996	5.0	GPL or proprietary
Oracle	Oracle Corporation	1977	10g Release 2	Proprietary
OpenLink Virtuoso	[OpenLink Software]	1998	4.5.3 (April 2006)	GPL or proprietary
PostgreSQL	PostgreSQL Global Development Group	June 1989	8.1.4	BSD
Pyrrho DBMS	University of Paisley	November 2005	0.5	Proprietary
SmallSQL	SmallSQL	April 16 2005	0.12	LGPL
SQLite	D. Richard Hipp	August 17 2000	3.3.6	Public domain
Teradata	Teradata	1984	V2R6.1	Proprietary
WX2	Kognitio	1988	5.x	Proprietary
	Maintainer	First public release date	Latest stable version	Software license

Operating system support

The operating systems the RDBMSes can run on.

	Windows	Mac OS X	Linux	BSD	UNIX	z/OS ¹
Adaptive Server Anywhere	Yes	Yes	Yes	No	Yes	No
Adaptive Server Enterprise	Yes	Yes	Yes	Yes	Yes	No
ADS	Yes	No	Yes	Yes	Yes	Yes
Apache Derby ²	Yes	Yes	Yes	Yes	Yes	Yes
DB2	Yes	No	Yes	No	Yes	Yes
Firebird	Yes	Yes	Yes	Yes	Yes	Maybe
HSQLDB ²	Yes	Yes	Yes	Yes	Yes	Yes
H2 ²	Yes	Yes	Yes	Yes	Yes	Maybe
Informix	Yes	Yes	Yes	Yes	Yes	No
Ingres	Yes	Yes	Yes	Yes	Yes	Maybe
InterBase	Yes	No	Yes	No	Yes (Solaris)	No
Adabas	Yes	No	Yes	No	Yes	Yes
MaxDB	Yes	No	Yes	No	Yes	Maybe
Microsoft SQL Server	Yes	No	No	No	No	No
MySQL	Yes	Yes	Yes	Yes	Yes	Maybe
Oracle	Yes	Yes	Yes	No	Yes	Yes
OpenLink Virtuoso	Yes	Yes	Yes	Yes	Yes	Yes
PostgreSQL	Yes	Yes	Yes	Yes	Yes	Maybe
Pyrrho DBMS	Yes (.NET)	No	Yes (Mono)	No	No	No
SmallSQL	Yes	Yes	Yes	Yes	Yes	Yes
SQLite	Yes	Yes	Yes	Yes	Yes	Maybe
Teradata	Yes	No	Yes	No	Yes	No
	Windows	Mac OS X	Linux	BSD	UNIX	z/OS ¹

Note (1): Open source databases listed as UNIX-compatible will likely compile and run under z/OS's built-in UNIX System Services (USS) subsystem. Most databases listed as Linux-compatible can run alongside z/OS on the same server using Linux on zSeries.

Note (2): The database availability depends on Java Virtual Machine not on the operating system

Fundamental features

Information about what fundamental RDBMS features are implemented natively.

	ACID	Referential integrity	Transactions	Unicode
Adaptive Server Anywhere	Yes	Yes	Yes	Yes
Adaptive Server Enterprise	Yes	Yes	Yes	Yes
ADS	Yes	Yes	Yes	Yes
Apache Derby	Yes	Yes	Yes	Yes
DB2	Yes	Yes	Yes	Yes
Firebird	Yes	Yes	Yes	Yes
HSQLDB	Yes	Yes	Yes	Yes
H2	Yes	Yes	Yes	Yes
Informix	Yes	Yes	Yes	Yes
Ingres	Yes	Yes	Yes	Yes
InterBase	Yes	Yes	Yes	Yes
MaxDB	Yes	Yes	Yes	Yes
Microsoft SQL Server	Yes	Yes	Yes	Yes
MySQL	Depends ³	Depends ³	Depends ³	Yes / UTF-8 (3-byte) & UCS-2
Oracle	Yes	Yes	Yes	Yes
OpenLink Virtuoso	Yes	Yes	Yes	Yes
PostgreSQL	Yes	Yes	Yes	Yes / UTF-8 (4-byte)
Pyrrho DBMS	Yes	Yes	Yes	Yes
SQLite	Yes	No ⁴	Basic ⁴	Yes
Teradata	Yes	Yes	Yes	Yes
	ACID	Referential integrity	Transactions	Unicode

Note (3): For transactions and referential integrity, the InnoDB table type must be used; the default table type, MyISAM, does not support these features. However, even the InnoDB table type permits storage of values that exceed the data range; some view this as violating the Integrity constraint of ACID.

Note (4): FOREIGN KEY constraints are parsed but are not enforced. Triggers can be used instead. Nested transactions are not supported. [link]

Tables and views

Information about what tables and views (other than basic ones) are supported natively.

	Temporary table	Materialized view
Adaptive Server Anywhere	Yes	Will be in 10.0
Adaptive Server Enterprise	Yes ⁵	No
ADS	Yes	Yes
Apache Derby	Yes	No
DB2	Yes	Yes
Firebird	No	No
HSQLDB	Yes	No
H2	Yes	No
Informix	Yes	Yes
Ingres	Yes	Ingres r4
InterBase	Yes	No
MaxDB	Yes	No
Microsoft SQL Server	Yes	Yes
MySQL	Yes	Similar ⁶
Oracle	Yes	Yes
OpenLink Virtuoso	Yes	Yes
PostgreSQL	Yes	Similar ⁷
Pyrrho DBMS	No	No
SQLite	Yes	No
Teradata	Yes	Yes
	Temporary table	Materialized view

Note (5): Server provides tempdb, which can be used for public and private (for the session) temp tables. [link]

Note (6): As with Postgres, materialized views can be emulated using stored procedures and triggers. Here is one example: [link].

Note (7): Materialized views can be implemented with PL/pgSQL, PL/Perl, PL/Python, or other procedural languages. One example is here: [link].

Indices

Information about what indices (other than basic B-/B+ tree indices) are supported natively.

	R-/R+ tree	Hash	Expression	Partial	Reverse	Bitmap	GiST
Adaptive Server Anywhere
Adaptive Server Enterprise	No	No	No	No	Yes	No	No
ADS	Yes	Yes	Yes	No	Yes	Yes	No
Apache Derby	No	No	No	No	No	No	No
DB2	No	?	No	No	Yes	Yes	No
Firebird	No	No	No	No	No	No	No
HSQLDB	No	No	No	No	No	No	No
H2	No	No	No	No	No	No	No
Informix	Yes	Yes	Yes	Yes	Yes	Yes	No
Ingres	Yes	Yes	Ingres r4	No	No	Ingres r4	No
InterBase	No	No	No	No	No	No	No
MaxDB	?	?	No	No	No	No	No
Microsoft SQL Server	?	Non/Cluster & fill factor	Yes ⁸	Yes ⁹	Yes ⁸	No	No
MySQL	in SQL 5.0 MyISAM, BDB, or InnoDB tables	HEAP tables only	No	No	No	No	No
Oracle	EE edition only	Cluster Tables	Yes	No	Yes	Yes	No
OpenLink Virtuoso	Yes	Cluster	Yes	No	No	No	No
PostgreSQL	Yes	Yes	Yes	Yes	Yes ¹⁰	Yes	Yes
Pyrrho DBMS	No	No	No	No	No	No	No
SQLite	No	No	No	No	Yes	No	No
Teradata	No	Yes	Yes	Yes	No	Yes	No
	R-/R+ tree	Hash	Expression	Partial	Reverse	Bitmap	GiST

Note (8): Can be implemented by indexing a computed column or by using an indexed view. [link]

Note (9): Can be implemented by using an indexed view. [link]

Note (10): A PostgreSQL functional index can be used to reverse the order of a field.

Other objects

Information about what other objects are supported natively.

	Domain	Cursor	Trigger	Function ¹¹	Procedure ¹¹	External routine ¹¹
Adaptive Server Anywhere	Yes	Yes	Yes	Yes	Yes	Yes
Adaptive Server Enterprise	Yes	Yes	Yes	Yes	Yes	Yes
ADS	Yes	Yes	Yes	Yes	Yes	Yes
Apache Derby	No	Yes	Yes	Yes ¹²	Yes ¹²	Yes ¹²
DB2	No	Yes	Yes	Yes	Yes	Yes
Firebird	Yes	Yes	Yes	Yes	Yes	Yes
HSQLDB	?	No	Yes	Yes	Yes	Yes
H2	?	No	Yes	Yes	Yes	Yes
Informix	?	Yes	Yes	Yes	Yes	Yes
Ingres	Yes	Yes	Yes	Yes	Yes	Yes
InterBase	Yes	Yes	Yes	Yes	Yes	Yes
MaxDB	Yes	Yes	Yes	Yes	Yes	?
Microsoft SQL Server	Yes (2000 and beyond)	Yes	Yes	Yes	Yes	Yes
MySQL	No	Yes	Yes	Yes	Yes	Yes
Oracle	Yes	Yes	Yes	Yes	Yes	Yes
OpenLink Virtuoso	Yes	Yes	Yes	Yes	Yes	Yes
PostgreSQL	Yes	Yes	Yes	Yes	Yes	Yes
Pyrrho DBMS	Yes	Yes	Yes	Yes	Yes	Yes
SQLite	No	No	Yes	No	No	Yes
Teradata	No	Yes	Yes	Yes	Yes	Yes
	Domain	Cursor	Trigger	Function	Procedure	External routine

Note (11): Both function and procedure refer to internal routines written in SQL and/or procedural language like PL/SQL. External routine refers to the one written in the host languages, such as C, Java, Cobol, etc. "Stored procedure" is a commonly used term for these routine types. However, its definition varies between different database vendors.
Note (12): In Derby, users code functions and procedures in Java.

Partitioning

Information about what partitioning methods are supported natively.

	Range	Hash	Composite (Range+Hash)	List
Adaptive Server Anywhere	?	?	?	?
Adaptive Server Enterprise	Yes	Yes	No	Yes
ADS	Yes	Yes	Yes	Yes
Apache Derby	No	No	No	No
DB2	Yes	Yes	Yes	Yes
Firebird	No	No	No	No
Informix	Yes	Yes	?	?
Ingres	Yes	Yes	Yes	Yes
InterBase	No	No	No	No
MaxDB	No	No	No	No
Microsoft SQL Server	Yes	No	No	No
MySQL	Yes (5.1)	Yes (5.1)	Yes (5.1)	Yes (5.1)
Oracle	Yes	Yes	Yes	Yes
OpenLink Virtuoso	Yes	No	No	No
PostgreSQL	Yes ¹³	Yes ¹³	Yes ¹³	No
Pyrrho DBMS	No	No	No	No
SQLite	No	No	No	No
Teradata	Yes	Yes	Yes	Yes
	Range	Hash	Composite (Range+Hash)	List

Note (13): PostgreSQL 8.1 provides partitioning support through check constraints. Range, List and Hash methods can be emulated with PL/pgSQL or other procedural languages. [link]

External links

[Comparison of different SQL implementations against SQL standards]. Includes Oracle, DB2, Microsoft SQL Server, MySQL and PostgreSQL.
[Comparison of Oracle 8/9i, MySQL 4.x and PostgreSQL 7.x DBMS against SQL standards].
[Comparison of geometrical data handling in PostgreSQL, MySQL and DB2]
[Open Source Database Software Comparison]
[PostgreSQL vs. MySQL vs. Commercial Databases: It's All About What You Need]
[The SQL92 standard]
[Comparison of Oracle and SQL Server]

From Wikipedia, the Free Encyclopedia. Original article here. Support Wikipedia by contributing or donating.
All text is available under the terms of the GNU Free Documentation License See Wikipedia Copyrights for details.