Comparison of relational database management systems

From Wikipedia, the free encyclopedia

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 Limits 5 Tables and views 6 Indexes 7 Database capabilities 8 Other objects 9 Partitioning 10 See also 11 External links

[edit] General information

	Maintainer	First public release date	Latest stable version	Software license
4th Dimension	4D s.a.s	1984	v11 SQL	Proprietary
ADABAS	Software AG	1970	?	?
Adaptive Server Enterprise	Sybase	1987	15.0	Proprietary
Advantage Database Server	Sybase	1992	8.1	Proprietary
Apache Derby	Apache	2004	10.4.1.3	Apache License
DB2	IBM	1982	9.5	Proprietary
DBISAM	Elevate Software	?	4.25	Proprietary
Datawasp	Significant Data Systems	April 2008	1.0.1	Proprietary
ElevateDB	Elevate Software	?	1.01	Proprietary
FileMaker	FileMaker	20000725 1984	9	proprietary
Firebird	Firebird project	20000725July 25, 2000	2.1.0	IPL and IDPL
Informix	IBM	1985	11.10	Proprietary
HSQLDB	HSQL Development Group	2001	1.8.0	BSD
H2	H2 Software	2005	1.0	MPL
Ingres	Ingres Corp.	1974	Ingres 2006 r2 9.1.0	GPL and proprietary
InterBase	CodeGear	1985	2007	Proprietary
MaxDB	SAP AG	?	7.6	GPL or proprietary
Microsoft Access	Microsoft	1992	12 (2007)	Proprietary
Microsoft Visual Foxpro	Microsoft	?	9 (2005)	Proprietary
Microsoft SQL Server	Microsoft	1989	9.00.3042 (2005 SP2)	Proprietary
MonetDB	The MonetDB Developer Team	2004	4.16 (Feb. 2007)	MonetDB Public License v1.1
MySQL	Sun Microsystems	199611November 1996	5.0.51	GPL or proprietary
HP NonStop SQL	Hewlett-Packard	1987	SQL MX 2.0	Proprietary
Omnis Studio	TigerLogic Inc	198207July 1982	4.3.1 Release 1 (May 2008)	Proprietary
Oracle	Oracle Corporation	197911November 1979	11g Release 1 (September 2007)	Proprietary
Oracle Rdb	Oracle Corporation	1984	7.2	Proprietary
OpenEdge	Progress Software Corporation	1984	10.1C	Proprietary
OpenLink Virtuoso	OpenLink Software	1998	5.0.5 (January 2008)	GPL or proprietary
Pervasive PSQL	Pervasive Software	?	9	Proprietary
Polyhedra DBMS	ENEA AB	1993	7.1	Proprietary
PostgreSQL	PostgreSQL Global Development Group	198906June 1989	8.3.1 (18 March 2008)	BSD
Pyrrho DBMS	University of Paisley	200511November 2005	0.5	Proprietary
ScimoreDB	Scimore	2005	2.5	Freeware
SmallSQL	SmallSQL	20050416April 16, 2005	0.19	LGPL
SQL Anywhere	Sybase	1992	10.0	Proprietary
SQLite	D. Richard Hipp	20000817August 17, 2000	3.5.7 (17 March 2008)	Public domain
Teradata	Teradata	1984	V12	Proprietary
Valentina	Paradigma Software	199802February 1998	3.0.1	Proprietary

[edit] Operating system support

The operating systems the RDBMSes can run on.

	Windows	Mac OS X	Linux	BSD	UNIX	z/OS 1
4th Dimension	Yes	Yes	No	No	No	No
ADABAS	Yes	No	Yes	No	Yes	Yes
Adaptive Server Enterprise	Yes	Yes	Yes	Yes	Yes	No
Advantage Database Server	Yes	No	Yes	No	No	No
Apache Derby 2	Yes	Yes	Yes	Yes	Yes	Yes
Datawasp	Yes	No	No	No	No	No
DB2 5	Yes	No	Yes	No	Yes	Yes
Firebird	Yes	Yes	Yes	Yes	Yes	Maybe
HSQLDB 2	Yes	Yes	Yes	Yes	Yes	Yes
H2 2	Yes	Yes	Yes	Yes	Yes	Maybe
FileMaker	Yes	Yes	No	No	No	No
Informix	Yes	Yes	Yes	Yes	Yes	No
Ingres	Yes	Yes	Yes	Yes	Yes	Partial
InterBase	Yes	Yes	Yes	No	Yes (Solaris)	No
MaxDB	Yes	No	Yes	No	Yes	Maybe
Microsoft Access	Yes	No	No	No	No	No
Microsoft Visual Foxpro	Yes	No	No	No	No	No
Microsoft SQL Server	Yes	No	No	No	No	No
MonetDB	Yes	Yes	Yes	No	Yes	No
MySQL	Yes	Yes	Yes	Yes	Yes	Maybe
Omnis Studio	Yes	Yes	Yes	No	No	No
Oracle 4	Yes	Yes	Yes	No	Yes	Yes
Oracle Rdb 3	No	No	No	No	No	No
OpenEdge	Yes	No	Yes	No	Yes	No
OpenLink Virtuoso	Yes	Yes	Yes	Yes	Yes	Yes
Polyhedra DBMS	Yes	No	Yes	No	Yes	No
PostgreSQL	Yes	Yes	Yes	Yes	Yes	No
Pyrrho DBMS	Yes (.NET)	No	Yes (Mono)	No	No	No
ScimoreDB	Yes	No	No	No	No	No
SmallSQL 2	Yes	Yes	Yes	Yes	Yes	Yes
SQL Anywhere	Yes	Yes	Yes	No	Yes	No
SQLite	Yes	Yes	Yes	Yes	Yes	Maybe
Teradata	Yes	No	Yes	No	Yes	No
Valentina	Yes	Yes	Yes	No	No	No

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

Note (3): Oracle Rdb was originally developed by DEC, and runs on OpenVMS

Note (4): Oracle database 11g also runs on OpenVMS, HP/UX and AIX. 10g also supported BS2000/OSD and z/OS (31-bit), but that support has been discontinued in 11g. Earlier versions than 10g were available on a wide variety of platforms.

Note (5): DB2 is also available for i5/OS, z/VM, z/VSE. Previous versions were also available for OS/2.

[edit] Fundamental features

Information about what fundamental RDBMS features are implemented natively.

	ACID	Referential integrity	Transactions	Unicode	Interface
4th Dimension	?	?	?	?	?
ADABAS	?	?	?	?	?
Adaptive Server Enterprise	Yes	Yes	Yes	Yes	?
Advantage Database Server	Yes	Yes	Yes	No	API & SQL
Apache Derby	Yes	Yes	Yes	Yes	SQL
Datawasp	No	Yes	Yes	Yes	GUI
DB2	Yes	Yes	Yes	Yes	SQL
Firebird	Yes	Yes	Yes	Yes	SQL
HSQLDB	Yes	Yes	Yes	Yes	SQL
H2	Yes	Yes	Yes	Yes	SQL
Informix	Yes	Yes	Yes	Yes	?
Ingres	Yes	Yes	Yes	Yes	SQL
InterBase	Yes	Yes	Yes	Yes	SQL
MaxDB	Yes	Yes	Yes	Yes	SQL
Microsoft Access	No	Yes	Yes	Yes	GUI & SQL
Microsoft Visual Foxpro	No	Yes	Yes	No	GUI & SQL
Microsoft SQL Server	Yes	Yes	Yes	Yes	SQL
MonetDB	Yes	Yes	Yes	Yes	?
MySQL	Yes 6	Yes 6	Yes 6	Partial	SQL
Oracle	Yes	Yes	Yes	Yes	SQL
Oracle Rdb	Yes	Yes	Yes	Yes	?
OpenEdge	Yes	No 7	Yes	Yes	?
OpenLink Virtuoso	Yes	Yes	Yes	Yes	?
Polyhedra DBMS	Yes	Yes	Yes	Yes	SQL
PostgreSQL	Yes	Yes	Yes	Yes	SQL
Pyrrho DBMS	Yes	Yes	Yes	Yes	?
ScimoreDB	Yes	Yes	Yes	Partial	SQL
SQL Anywhere	Yes	Yes	Yes	Yes	?
SQLite	Yes	No 8	Basic 8	Yes	SQL
Teradata	Yes	Yes	Yes	Yes	SQL
Valentina	No	Yes	No	Yes	?

Note (6): For transactions and referential integrity, the InnoDB table type must be used; Windows installer sets this as default if support for transactions is selected, on other operating systems the default table type is MyISAM. 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 (7): FOREIGN KEY constraints are parsed but are not enforced. Triggers can be used instead. Nested transactions are not supported. [1]

Note (8): Available via Triggers.

[edit] Limits

Information about data size limits.

	Max DB size	Max table size	Max row size	Max columns per row	Max Blob/Clob size	Max CHAR size	Max NUMBER size
Advantage Database Server	Unlimited	16 EB (16 EiB)	65530 B	65135/(10+AverageFieldNameLength)	4 GB (4 GiB)	?	64 bits
Datawasp	Unlimited	2 GB	32,678	256	2 GB	text1024/RTF-Unlimited	64 bits
DB2	512 TB (512 TiB)	512 TB	32,677 B	1012	2 GB	32 KB (32 KiB)	64 bits
Firebird	Unlimited 1	~32 TB	65,536 B	Depends on data types used.	2 GB	32,767 B	64 bits
Ingres	Unlimited	Unlimited	256 KB	1024	2 GB	32,000 B	64 bits
Microsoft Access	2 GB	2 GB	16 MB	255	64 KB (memo field), 1 GB ("OLE Object" field)	255 B (text field)	32 bits
Microsoft Visual Foxpro	2 GB	2 GB	16 MB	255	2 GB	16 MB	32 bits
Microsoft SQL Server	524,258 TB (32,767 files * 16 TB max file size)	524,258 TB	8060 B (Sql Server 2000 and before) to Unknown (Sql Server 2005)	1024	2 GB	8000 B	64 bits
MySql 5	Unlimited	2 GB (Win32 FAT32) to 16 TB (Solaris)	64 KB	3398	4 GB (longtext, longblob)	64 KB (text)	64 bits
Oracle	Unlimited (4 GB * block size per tablespace)	4 GB * block size (with BIGFILE tablespace)	Unlimited	1000	4 GB (or max datafile size for platform)	4000 B	126 bits
OpenEdge	Around 32 Exabytes	1 Petabyte	32Kb	1000	1 GB	2000 B	64 bits
Polyhedra DBMS	limited only by available RAM, address space	232 rows	Unlimited	65536	4 GB (subject to RAM)	4 GB (subject to RAM)	32 bits
PostgreSQL	Unlimited	32 TB	1.6 TB	250-1600 depending on type	1 GB (text, bytea) - stored inline	1 GB	Unlimited
ScimoreDB	Unlimited	16 EB	8050 B	255	16 TB	8000 B	64 bits
Teradata	Unlimited	Unlimited	64 KB wo/lobs(64 GB w/lobs)	2048	2 GB	10,000	64 bits

Note (1): Firebird 2.x maximum database size is effectively unlimited with the largest known database size >980GB[2]. Firebird 1.5.x maximum database size: 32 TB.

[edit] Tables and views

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

	Temporary table	Materialized view
4th Dimension	?	?
ADABAS	?	?
Adaptive Server Enterprise	Yes  1	No
Advantage Database Server	Yes	No (only common views)
Apache Derby	Yes	No
Datawasp	Yes	Yes
DB2	Yes	Yes
Firebird	Yes	No (only common views)
HSQLDB	Yes	No
H2	Yes	No
Informix	Yes	Yes
Ingres	Yes	Planned for inclusion in next major release
InterBase	Yes	No
MaxDB	Yes	No
Microsoft Access	Yes	No
Microsoft Visual Foxpro	Yes	Yes
Microsoft SQL Server	Yes	Yes 2
MonetDB	Yes	No
MySQL	Yes	No 3
Oracle	Yes	Yes
Oracle Rdb	Yes	Yes
OpenEdge	Yes	No
OpenLink Virtuoso	Yes	Yes
Polyhedra DBMS	No	No (only common views)
PostgreSQL	Yes	No 4
Pyrrho DBMS	No	No
SQL Anywhere	Yes	Yes
ScimoreDB	No	No
SQLite	Yes	No
Teradata	Yes	Yes
Valentina	Yes	No

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

Note (2): Query optimizer support only in Developer and Enterprise Editions. In other versions, a direct reference to materialized view and a query hint are required. [4].

Note (3): Materialized views can be emulated using stored procedures and triggers.[5].

Note (4): Materialized views can be emulated with stored procedures and triggers using PL/pgSQL, PL/Perl, PL/Python, or other procedural languages.[6].

[edit] Indexes

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

	R-/R+ tree	Hash	Expression	Partial	Reverse	Bitmap	GiST	GIN
4th Dimension	?	?	?	?	?	?	?	?
ADABAS	?	?	?	?	?	?	?	?
Adaptive Server Enterprise	No	No	No	No	Yes	No	No	No
Apache Derby	No	No	No	No	No	No	No	No
DB2	No	?	Yes	No	Yes	Yes	No	No
Firebird	No	No	Yes	No	Yes 16	No	No	No
HSQLDB	No	No	No	No	No	No	No	No
H2	No	Yes	No	No	No	No	No	No
Informix	Yes	Yes	Yes	Yes	Yes	Yes	No	No
Ingres	Yes	Yes	Ingres r4	No	No	Ingres r4	No	No
InterBase	No	No	No	No	No	No	No	No
MaxDB	No	No	No	No	No	No	No	No
Microsoft Access	No	No	No	No	No	No	No	No
Microsoft Visual Foxpro	No	No	Yes	Yes	Yes 18	Yes	No	No
Microsoft SQL Server	?	Non/Cluster & fill factor	Yes 8	Yes 9	Yes 8	No	No	No
MonetDB	No	Yes	No	No	No	No	No	No
MySQL	MyISAM tables only	MEMORY, Cluster (NDB), InnoDB,17 tables only	No	No	No	No	No	No
Oracle	EE edition only	Cluster Tables	Yes	Yes 15	Yes	Yes	No	No
Oracle Rdb	No	Yes	?	No	No	?	No	No
OpenLink Virtuoso	Yes	Cluster	Yes	No	No	Yes	No	No
Polyhedra DBMS	No	Yes	No	No	No	No	No	No
PostgreSQL	Yes	Yes	Yes	Yes	Yes 10	Yes 11	Yes	Yes
Pyrrho DBMS	No	No	No	No	No	No	No	No
ScimoreDB	No	No	No	No	No	No	No	No
SQL Anywhere	No	No	No	No	No	No	No	No
SQLite	No	No	No	No	Yes	No	No	No
Teradata	No	Yes	Yes	Yes	No	Yes	No	No
Valentina	No	No	Yes 8	Yes 17	Yes	Yes	No	No

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

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

Note (17): InnoDB automatically generates adaptive hash index entries as needed.

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

Note (11): PostgreSQL will likely support on-disk bitmap indexes in 8.4. Version 8.2 supports a related technique known as "in-memory bitmap scans".

Note (15): Can be implemented using Function-based Indexes in Oracle 8i and higher, but the function needs to be used in the sql for the index to be used.

Note (16): The users need to use a function from freeAdhocUDF library or similar. [9]

Note (17): Can be implemented using Function-based Indexes in Valentina.

Note (18): Can be implemented for most data types using expression-based indexes.

[edit] Database capabilities

	Union	Inner joins	Outer joins	Inner selects	Merge	Blobs and Clobs
4th Dimension	?	?	?	?	?	?
ADABAS	?	?	?	?	?	?
Adaptive Server Enterprise	Yes	Yes	Yes	Yes	No	Yes
Advantage Database Server	Yes	Yes	Yes	Yes	Yes	Yes
Apache Derby	Yes	Yes	Yes	?	?	Yes
Datawasp	Yes	Yes	Yes	Yes	Yes	Yes
DB2	Yes	Yes	Yes	Yes	Yes	Yes
Firebird	Yes	Yes	Yes	Yes	Yes	Yes
HSQLDB	Yes	Yes	Yes	?	?	?
H2	Yes	Yes	Yes	?	?	Yes
Informix	Yes	Yes	Yes	Yes	Yes	Yes
Ingres	Yes	Yes	Yes	Yes	Yes	Yes
InterBase	Yes	Yes	Yes	?	?	Yes
MaxDB	Yes	Yes	Yes	Yes	No	Yes
Microsoft Access	Yes	Yes	Yes	Yes	?	Yes
Microsoft Visual Foxpro	Yes	Yes	Yes	Yes	?	Yes
Microsoft SQL Server	Yes	Yes	Yes	Yes	Yes	Yes
MonetDB	?	?	?	?	?	?
MySQL	Yes	Yes	Yes	Yes	Yes	Yes
Oracle	Yes	Yes	Yes	Yes	Yes	Yes
Oracle Rdb	Yes	Yes	Yes	Yes	Yes	Yes
OpenEdge	Yes	Yes	Yes	?	?	Yes
OpenLink Virtuoso	Yes	Yes	Yes	Yes	?	Yes
Polyhedra DBMS	Yes	Yes	No	?	?	Yes
PostgreSQL	Yes	Yes	Yes	Yes	Yes	Yes
Pyrrho DBMS	?	?	?	?	?	?
ScimoreDB	Yes	Yes	LEFT only	Yes	Yes	Yes
SmallSQL	?	?	?	?	?	?
SQL Anywhere	Yes	Yes	Yes	Yes	?	?
SQLite	Yes	Yes	LEFT only	?	?	Yes
Teradata	Yes	Yes	Yes	Yes	Yes	Yes
Valentina	Yes	Yes	Yes	Yes	Yes	Yes

[edit] Other objects

Information about what other objects are supported natively.

	Data Domain	Cursor	Trigger	Function 12	Procedure 12	External routine 12
4th Dimension	?	?	?	?	?	?
ADABAS	?	?	?	Yes?	Yes?	?
Adaptive Server Enterprise	Yes	Yes	Yes	Yes	Yes	Yes
Advantage Database Server	Yes	Yes	Yes	Yes	Yes	Yes
Apache Derby	No	Yes	Yes	Yes 13	Yes 13	Yes 13
DB2	No	Yes	Yes	Yes	Yes	Yes
Firebird	Yes	Yes	Yes	Yes	Yes	Yes
HSQLDB	?	No	Yes	Yes	Yes	Yes
H2	Yes	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 Access	No	No	No	No	No	Yes
Microsoft Visual Foxpro	No	Yes	Yes	Yes	Yes	Yes
Microsoft SQL Server	Yes (2000 and beyond)	Yes	Yes	Yes	Yes	Yes
MonetDB	No	No	Yes	Yes	Yes	Yes
MySQL	No	Yes	Yes	Yes	Yes	Yes
OpenEdge	Yes	Yes	Yes	Yes	Yes	Yes
Oracle	Yes	Yes	Yes	Yes	Yes	Yes
Oracle Rdb	Yes	Yes	Yes	Yes	Yes	Yes
OpenLink Virtuoso	Yes	Yes	Yes	Yes	Yes	Yes
Polyhedra DBMS	No	No	Yes	Yes	Yes	Yes
PostgreSQL	Yes	Yes	Yes	Yes	Yes	Yes
Pyrrho DBMS	Yes	Yes	Yes	Yes	Yes	Yes
ScimoreDB	No	No	No	No	Yes	Yes
SQL Anywhere	Yes	Yes	Yes	Yes	Yes	Yes
SQLite	No	No	Yes	No	No	Yes
Teradata	No	Yes	Yes	Yes	Yes	Yes
Valentina	No	Yes	Yes	Yes	Yes	No

Note (12): 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 (13): In Derby, users code functions and procedures in Java.

[edit] Partitioning

Information about what partitioning methods are supported natively.

	Range	Hash	Composite (Range+Hash)	List	Shadow	Native Replication API
4th Dimension	?	?	?	?	?	?
ADABAS	?	?	?	?	?	?
Adaptive Server Enterprise	Yes	Yes	No	Yes	?	?
Apache Derby	No	No	No	No	?	?
IBM DB2	Yes	Yes	Yes	Yes	?	?
Firebird	No	No	No	No	Yes	No
HSQLDB	?	?	?	?	?	?
Informix	Yes	Yes	Yes	Yes	?	?
Ingres	Yes	Yes	Yes	Yes	No	No
InterBase	No	No	No	No	Yes	Yes
MaxDB	No	No	No	No	?	?
Microsoft Access	No	No	No	No	No	No
Microsoft Visual Foxpro	No	No	No	No	No	No
Microsoft SQL Server	Yes	No	No	No	?	?
MonetDB	Yes (M5)	Yes (M5)	Yes (M5)	No	?	?
MySQL	Yes (5.1)	Yes (5.1)	Yes (5.1)	Yes (5.1)	?	?
Oracle	Yes	Yes	Yes	Yes	?	?
Oracle Rdb	Yes	Yes	?	?	?	?
OpenLink Virtuoso	Yes	No	No	No	?	?
Polyhedra DBMS	No	No	No	No	?	?
PostgreSQL	Yes 14	Yes 14	Yes 14	Yes 14	?	?
Pyrrho DBMS	No	No	No	No	?	?
ScimoreDB	No	Yes	No	No	No	Yes
SQL Anywhere	No	No	No	No	?	?
SQLite	No	No	No	No	?	?
Teradata	Yes	Yes	Yes	Yes	?	?
Valentina	No	No	No	No	?	?

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

[edit] See also

• List of relational database management systems
• Comparison of object-relational database management systems
• Comparison of database tools

[edit] External links

• Comparison of different SQL implementations against SQL standards. Includes Oracle, DB2, Microsoft SQL Server, MySQL and PostgreSQL. (08/Jun/2007)
• Comparison of Oracle 8/9i, MySQL 4.x and PostgreSQL 7.x DBMS against SQL standards. (14/Mar/2005)
• Comparison of Oracle and SQL Server. (2004)
• Comparison of geometrical data handling in PostgreSQL, MySQL and DB2 (29/Sep/2003)
• Open Source Database Software Comparison (Mar/2005)
• PostgreSQL vs. MySQL vs. Commercial Databases: It's All About What You Need (12/Apr/2004)
• The SQL92 standard

v • d • e Database management systems Database models · Database normalization · Database storage · Distributed DBMS · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Object-relational database · Transaction processing Concepts Database · ACID · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key Objects Trigger · View · Table · Cursor · Log · Transaction · Index · Stored procedure · Partition SQL Select · Insert · Update · Merge · Delete · Join · Union · Create · Drop · Begin work · Commit · Rollback · Truncate · Alter Implementations Relational · Flat file · Deductive · Dimensional · Hierarchical · Network · Document-oriented · Object-oriented · Object-relational · Temporal · XML data stores · Triple stores Components Concurrency control · Data dictionary · JDBC · ODBC · Query language · Query optimizer · Query plan Database products: Object-oriented (comparison) · Relational (comparison) · Document-oriented