العربية
中国
Français
Deutsch
Ελληνική
हिन्दी
Italiano
日本語
Português
Русский
EspañolCOMPARISON 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.
The operating systems the RDBMSes can run on.
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
Information about what fundamental RDBMS features are implemented natively.
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]
Note (5): Available via Triggers.
Information about what tables and views (other than basic ones) are supported natively.
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].
Information about what indices (other than basic B-/B+ tree indices) are supported natively.
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]
Note (17): Can be implemented using Function-based Indexes in Valentina.
Information about what other objects are supported natively.
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.
Information about what partitioning methods are supported natively.
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]
The SQL specification makes clear what an "SQL schema" is; however, different databases implement it wrongly. To compound this confusion the functionality can, when wrongly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot "
A true fully (database, schema, and table) qualified query is exemplified as such:
Now, the issue, both a schema and a database can be used to isolate one table, "foo" from another like named table "foo". The following is pseudo code:
★
★
The problem that arises is that former MySQL users will mistakenly create multiple databases for one project. In this context MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, Postgres has rightfully applied more of the specification, in a sane-bottom-up approach, implementing cross-table, cross-schema, and then left room for future cross-database functionality.
MySQL aliases behind the scenes, ''schema'' with ''database'', such that
The end result is spin from both communities. While the Postgres community maintains that one database is all that is needed for one project; and MySQL, that schemas have no legitimate purpose when the functionality can be achieved with databases. Postgres adheres to more of the SQL specification, in a more intuitive fashion (bottom-up), while MySQL's counterargument allows their users to get the job done without any major drawback.
★ List of relational database management systems
★ Comparison of truly relational database management systems
★ Comparison of object-relational database management systems
★ 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
| Contents |
| General information |
| Operating system support |
| Fundamental features |
| Tables and views |
| Indices |
| Database capabilities |
| Other objects |
| Partitioning |
| Databases vs Schemas (terminology) |
| See also |
| External links |
General information
Operating system support
The operating systems the RDBMSes can run on.
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.
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]
Note (5): Available via Triggers.
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 | 5 | |
| Apache Derby | ||
| DB2 | ||
| Firebird | Will be in 2.1 | (only common views) |
| HSQLDB | ||
| H2 | ||
| Informix | ||
| Ingres | Ingres r4 | |
| InterBase | ||
| MaxDB | ||
| Microsoft SQL Server | ||
| MonetDB | ||
| MySQL | 6 | |
| Oracle | ||
| OpenEdge | ||
| OpenLink Virtuoso | ||
| PostgreSQL | 7 | |
| Pyrrho DBMS | ||
| SQL Anywhere | ||
| SQLite | ||
| Teradata | ||
| Valentina |
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].
Indices
Information about what indices (other than basic B-/B+ tree indices) are supported natively.
| R-/R+ tree | Hash | Expression | Partial | Reverse | Bitmap | GiST | GIN | |
|---|---|---|---|---|---|---|---|---|
| 4th Dimension | ||||||||
| Adabas | ||||||||
| Adaptive Server Enterprise | ||||||||
| Apache Derby | ||||||||
| DB2 | ||||||||
| Firebird | 16 | |||||||
| HSQLDB | ||||||||
| H2 | ||||||||
| Informix | ||||||||
| Ingres | Ingres r4 | |||||||
| InterBase | ||||||||
| MaxDB | ||||||||
| Microsoft SQL Server | Non/Cluster & fill factor | 8 | 9 | 8 | ||||
| MonetDB | ||||||||
| MySQL | MyISAM tables only | MEMORY, Cluster (NDB), InnoDB,17 tables only | ||||||
| Oracle | EE edition only | Cluster Tables | 15 | |||||
| OpenLink Virtuoso | Cluster | |||||||
| PostgreSQL | 10 | 11 | ||||||
| Pyrrho DBMS | ||||||||
| SQL Anywhere | ||||||||
| SQLite | ||||||||
| Teradata | ||||||||
| Valentina | 8 | 17 |
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]
Note (17): Can be implemented using Function-based Indexes in Valentina.
Database capabilities
Other objects
Information about what other objects are supported natively.
| Data Domain | Cursor | Trigger | Function 12 | Procedure 12 | External routine 12 | |
|---|---|---|---|---|---|---|
| 4th Dimension | ||||||
| Adabas | ? | ? | ||||
| Adaptive Server Enterprise | ||||||
| Apache Derby | 13 | 13 | 13 | |||
| DB2 | ||||||
| Firebird | ||||||
| HSQLDB | ||||||
| H2 | ||||||
| Informix | ||||||
| Ingres | ||||||
| InterBase | ||||||
| MaxDB | ||||||
| Microsoft SQL Server | (2000 and beyond) | |||||
| MonetDB | ||||||
| MySQL | ||||||
| OpenEdge | ||||||
| Oracle | ||||||
| OpenLink Virtuoso | ||||||
| PostgreSQL | ||||||
| Pyrrho DBMS | ||||||
| SQL Anywhere | ||||||
| SQLite | ||||||
| Teradata | ||||||
| Valentina |
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.
Partitioning
Information about what partitioning methods are supported natively.
| Range | Hash | Composite (Range+Hash) | List | |
|---|---|---|---|---|
| 4th Dimension | ||||
| Adabas | ||||
| Adaptive Server Enterprise | ||||
| Apache Derby | ||||
| IBM DB2 | ||||
| Firebird | ||||
| Informix | ||||
| Ingres | ||||
| InterBase | ||||
| MaxDB | ||||
| Microsoft SQL Server | ||||
| MonetDB | (M5) | (M5) | (M5) | |
| MySQL | (5.1.6) | (5.1.6) | (5.1.6) | (5.1.6) |
| Oracle | ||||
| OpenLink Virtuoso | ||||
| PostgreSQL | 14 | 14 | 14 | 14 |
| Pyrrho DBMS | ||||
| SQL Anywhere | ||||
| SQLite | ||||
| Teradata | ||||
| Valentina |
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]
Databases vs Schemas (terminology)
The SQL specification makes clear what an "SQL schema" is; however, different databases implement it wrongly. To compound this confusion the functionality can, when wrongly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot "
.". This seems to be a universal amongst all of the implementations.A true fully (database, schema, and table) qualified query is exemplified as such:
select
★ from database.schema.tableNow, the issue, both a schema and a database can be used to isolate one table, "foo" from another like named table "foo". The following is pseudo code:
★
select
★ from db1.foo vs. select
★ from db2.foo (no explicit schema between db and table)★
select
★ from [db1.]default.foo vs. select
★ from [db1.]alternate.foo (no explicit db prefix)The problem that arises is that former MySQL users will mistakenly create multiple databases for one project. In this context MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, Postgres has rightfully applied more of the specification, in a sane-bottom-up approach, implementing cross-table, cross-schema, and then left room for future cross-database functionality.
MySQL aliases behind the scenes, ''schema'' with ''database'', such that
create schema, and create database are analogs. It can be said, that MySQL therefore, has implemented cross-table functionality, skipped schema functionality entirely and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas, but lacks some functionality MySQL has with databases, while MySQL doesn't even attempt to support true schemas..The end result is spin from both communities. While the Postgres community maintains that one database is all that is needed for one project; and MySQL, that schemas have no legitimate purpose when the functionality can be achieved with databases. Postgres adheres to more of the SQL specification, in a more intuitive fashion (bottom-up), while MySQL's counterargument allows their users to get the job done without any major drawback.
See also
★ List of relational database management systems
★ Comparison of truly relational database management systems
★ Comparison of object-relational database management systems
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
This article provided by Wikipedia. To edit the contents of this article, click here for original source.
psst.. try this: add to faves
