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 |
[edit] General information
[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 |
| Adaptive Server Anywhere | Yes | Yes | Yes | No | Yes | No |
| Adaptive Server Enterprise | Yes | Yes | Yes | Yes | Yes | No |
| Apache Derby 2 | Yes | Yes | Yes | Yes | Yes | Yes |
| DB2 | 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 |
| Informix | Yes | Yes | Yes | Yes | Yes | No |
| Ingres | Yes | No | 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 |
| OpenEdge | Yes | No | Yes | No | Yes | No |
| OpenLink Virtuoso | Yes | Yes | Yes | Yes | Yes | Yes |
| PostgreSQL | Yes | Yes | Yes | Yes | Yes | No |
| 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 1 |
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
[edit] 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 |
| 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 | Yes 3 | Yes 3 | Yes 3 | Partial / UTF-8 (3-byte) & UCS-2 |
| Oracle | Yes | Yes | Yes | Yes |
| OpenEdge | Yes | No | 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 4 | Basic 4 | 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; 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 (4): FOREIGN KEY constraints are parsed but are not enforced. Triggers can be used instead. Nested transactions are not supported. [1]
[edit] 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 5 | No |
| Apache Derby | Yes | No |
| DB2 | Yes | Yes |
| Firebird | Will be in 2.1 | No(only common views) |
| 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 | No 6 |
| Oracle | Yes | Yes |
| OpenEdge | Yes | No |
| OpenLink Virtuoso | Yes | Yes |
| PostgreSQL | Yes | No 7 |
| 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.[2]
Note (6): Materialized views can be emulated using stored procedures and triggers.[3].
Note (7): Materialized views can be emulated with stored procedures and triggers using PL/pgSQL, PL/Perl, PL/Python, or other procedural languages.[4].
[edit] 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 |
| Apache Derby | No | No | No | No | No | No | No |
| DB2 | No | ? | No | No | Yes | Yes | No |
| Firebird | No | No | Yes | No | Yes 16 | No | No |
| HSQLDB | No | No | No | No | No | No | No |
| H2 | No | Yes | 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 8 | Yes 9 | Yes 8 | No | No |
| MySQL | MyISAM tables only | MEMORY, Cluster (NDB), InnoDB 17, tables only | No | No | No | No | No |
| Oracle | EE edition only | Cluster Tables | Yes | Yes 15 | Yes | Yes | No |
| OpenLink Virtuoso | Yes | Cluster | Yes | No | No | Yes | No |
| PostgreSQL | Yes | Yes | Yes | Yes | Yes 10 | No 11 | 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. [5]
Note (9): Can be implemented by using an indexed view. [6]
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.3. 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.
Note (16): The users need to use a function from freeAdhocUDF library or similar. [7]
[edit] Other objects
Information about what other objects are supported natively.
| Domain | Cursor | Trigger | Function 12 | Procedure 12 | External routine 12 | |
|---|---|---|---|---|---|---|
| Adaptive Server Anywhere | Yes | Yes | Yes | Yes | Yes | Yes |
| Adaptive Server Enterprise | 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 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 (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 | |
|---|---|---|---|---|
| Adaptive Server Anywhere | ? | ? | ? | ? |
| Adaptive Server Enterprise | Yes | Yes | No | Yes |
| Apache Derby | No | No | No | No |
| IBM 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 beta) | Yes (5.1 beta) | Yes (5.1 beta) | Yes (5.1 beta) |
| Oracle | Yes | Yes | Yes | Yes |
| OpenLink Virtuoso | Yes | No | No | No |
| PostgreSQL | Yes 14 | Yes 14 | Yes 14 | Yes 14 |
| Pyrrho DBMS | No | No | No | No |
| SQLite | No | No | No | No |
| Teradata | Yes | Yes | Yes | Yes |
| Range | Hash | Composite (Range+Hash) | List |
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. [8]
[edit] See also
- List of relational database management systems
- Comparison of truly relational database management systems
- Comparison of object-relational database management systems
[edit] External links
- Comparison of different SQL implementations against SQL standards. Includes Oracle, DB2, Microsoft SQL Server, MySQL and PostgreSQL. (22/Feb/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
