HOME

TheInfoList



OR:

The following tables compare general and technical information for a number of
relational database management system A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
s. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.


General information


Operating system support

The
operating system An operating system (OS) is system software that manages computer hardware, software resources, and provides common daemon (computing), services for computer programs. Time-sharing operating systems scheduler (computing), schedule tasks for ef ...
s that the RDBMSes can run on.


Fundamental features

Information about what fundamental RDBMS features are implemented natively. Note (1): Currently only supports read uncommited transaction isolation. Version 1.9 adds serializable isolation and version 2.0 will be fully ACID compliant. Note (2): MariaDB and MySQL provide ACID compliance through the default InnoDB storage engine. Note (3): "For other than
InnoDB InnoDB is a storage engine for the database management system MySQL and MariaDB. Since the release of MySQL 5.5.5 in 2010, it replaced MyISAM as MySQL's default table type. It provides the standard ACID-compliant transaction features, along ...
storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines." Note (4): Support for Unicode is new in version 10.0. Note (5): MySQL provides GUI interface through
MySQL Workbench MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system. It is the successor ...
. Note (6): OpenEdge SQL database engine uses Referential Integrity, OpenEdge ABL Database engine does not and is handled via database triggers.


Limits

Information about data size limits. Note (1): Firebird 2.x maximum database size is effectively unlimited with the largest known database size >980 GB. Firebird 1.5.x maximum database size: 32 TB. Note (2): Limit is 1038 using DECIMAL datatype. Note (3): InnoDB is limited to 8,000 bytes (excluding VARBINARY, VARCHAR, BLOB, or TEXT columns). Note (4): InnoDB is limited to 1,017 columns. Note (6): Using VARCHAR (MAX) in SQL 2005 and later. Note (7): When using a page size of 32 KB, and when BLOB/CLOB data is stored in the database file. Note (8): Java array size limit of 2,147,483,648 (231) objects per array applies. This limit applies to number of characters in names, rows per table, columns per table, and characters per CHAR/VARCHAR. Note (9): Despite the lack of a date datatype, SQLite does include date and time functions, which work for timestamps between 24 November 4714 B.C. and 1 November 5352. Note (10): Informix DATETIME type has adjustable range from YEAR only through 1/10000th second. DATETIME date range is 0001-01-01 00:00:00.00000 through 9999-12-31 23:59:59.99999. Note (11): Since version 12c. Earlier versions support up to 4000 B. Note (12): The 128PB limit refers to the storage limit of a single Informix server instance. Informix v12.10 and later versions support using sharding techniques to distribute a table across multiple server instances. A distributed Informix database has no upper limit on table or database size. Note (13): Informix DECIMAL type supports up to 32 decimal digits of precision with a range of 10^-130 to 10^125. Fixed and variable precision are supported.


Tables and views

Information about what tables and views (other than basic ones) are supported natively. Note (1): Server provides tempdb, which can be used for public and private (for the session) temp tables. Note (2): Materialized views are not supported in Informix; the term is used in IBM's documentation to refer to a temporary table created to run the view's query when it is too complex, but one cannot for example define the way it is refreshed or build an index on it. The term is defined in the Informix Performance Guide. Note (4): Materialized views can be emulated using
stored procedure A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data d ...
s and triggers.


Indexes

Information about what indexes (other than basic B-/
B+ tree A B+ tree is an m-ary tree with a variable but often large number of children per node. A B+ tree consists of a root, internal nodes and leaves. The root may be either a leaf or a node with two or more children. A B+ tree can be viewed as a ...
indexes) are supported natively. Note (1): The users need to use a function from freeAdhocUDF library or similar. Note (2): Can be implemented for most data types using expression-based indexes. Note (3): Can be emulated by indexing a computed column (doesn't easily update) or by using an "Indexed View" (proper name not just any view works). Note (4): Used for InMemory ColumnStore index, temporary hash index for hash join, Non/Cluster & fill factor. Note (5): InnoDB automatically generates adaptive hash index entries as needed. Note (6): 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 (7): A PostgreSQL functional index can be used to reverse the order of a field. Note (10): B+ tree and full-text only for now. Note (11): R-Tree indexing available in base edition with Locator but some functionality requires Personal Edition or Enterprise Edition with Spatial option.


Database capabilities

Note (1): Recursive CTEs introduced in 11gR2 supersedes similar construct called CONNECT BY.


Data types


Other objects

Information about what other objects are supported natively. Note (1): 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 A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDBMS). Such procedures are stored in the database data d ...
" is a commonly used term for these routine types. However, its definition varies between different database vendors. Note (2): In Derby, H2, LucidDB, and CUBRID, users code functions and procedures in Java. Note (3): ENUM datatype exists. CHECK clause is parsed, but not enforced in runtime. Note (5): Informix supports external functions written in Java, C, & C++.


Partitioning

Information about what partitioning methods are supported natively. Note (1): Raima Database Manager 14.0 requires the application programs to select the correct partition (using range, hash or composite techniques) when adding data, but the ''database union'' functionality allows all partitions to be read as a single database.


Access control

Information about access control functionalities. Note (1): Network traffic could be transmitted in a secure way (not clear-text, in general SSL encryption). Precise if option is default, included option or an extra modules to buy. Note (2): Options are present to set a minimum size for password, respect complexity like presence of numbers or special characters. Note (3): How do you get security updates? Is it free access, do you need a login or to pay? Is there easy access through a Web/FTP portal or RSS feed or only through offline access (mail CD-ROM, phone). Note (4): Does database process run as root/administrator or unprivileged user? What is default configuration? Note (5): Is there a separate user to manage special operation like backup (only dump/restore permissions), security officer (audit), administrator (add user/create database), etc.? Is it default or optional? Note (6): Common Criteria certified product list. Note (7): FirebirdSQL seems to only have SYSDBA user and DB owner. There are no separate roles for backup operator and security administrator. Note (8): User can define a dedicated backup user but nothing particular in default install. Note (9): Authentication methods. Note (10): Informix Dynamic Server supports PAM and other configurable authentication. By default uses OS authentication. Note (11): Authentication methods. Note (12): With the use of Pervasive AuditMaster. Note (13): User-based security is optional in Polyhedra, but when enabled can be enhanced to a role-based model with auditing.


Databases vs schemas (terminology)

The SQL specification defines what an "SQL schema" is; however, databases implement it differently. To compound this confusion the functionality can overlap with that of a parent database. An SQL schema is simply a
namespace In computing, a namespace is a set of signs (''names'') that are used to identify and refer to objects of various kinds. A namespace ensures that all of a given set of objects have unique names so that they can be easily identified. Namespaces ...
within a database; things within this namespace are addressed using the member operator dot ".". This seems to be a universal among all of the implementations. A true fully (database, schema, and table) qualified query is exemplified as such: SELECT * FROM database.schema.table 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 database1.foo vs. SELECT * FROM database2.foo (no explicit schema between database and table) * SELECT * FROM atabase1.efault.foo vs. SELECT * FROM atabase1.lternate.foo (no explicit database prefix) The problem that arises is that former
MySQL MySQL () is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database ...
users will create multiple databases for one project. In this context, MySQL databases are analogous in function to PostgreSQL-schemas, insomuch as PostgreSQL deliberately lacks off-the-shelf cross-database functionality (preferring multi-tenancy) that MySQL has. Conversely,
PostgreSQL PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the ...
has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality. MySQL aliases ''schema'' with ''database'' behind the scenes, such that CREATE SCHEMA and CREATE DATABASE are analogs. It can therefore be said that MySQL has implemented cross-database functionality, skipped schema functionality entirely, and provided similar functionality into their implementation of a database. In summary, PostgreSQL fully supports schemas and multi-tenancy by strictly separating databases from each other and thus lacks some functionality MySQL has with databases, while MySQL does not even attempt to support standard schemas. Oracle has its own spin where creating a user is synonymous with creating a schema. Thus a database administrator can create a user called PROJECT and then create a table PROJECT.TABLE. Users can exist without schema objects, but an object is always associated with an owner (though that owner may not have privileges to connect to the database). With the 'shared-everything' Oracle RAC architecture, the same database can be opened by multiple servers concurrently. This is independent of replication, which can also be used, whereby the data is copied for use by different servers. In the Oracle implementation, a 'database' is a set of files which contains the data while the 'instance' is a set of processes (and memory) through which a database is accessed. Informix supports multiple databases in a server instance like MySQL. It supports the CREATE SCHEMA syntax as a way to group DDL statements into a single unit creating all objects created as a part of the schema as a single owner. Informix supports a database mode called ANSI mode which supports creating objects with the same name but owned by different users. PostgreSQL and some other databases have support for foreign schemas, which is the ability to import schemas from other servers as defined in ISO/IEC 9075-9 (published as part of SQL:2008). This appears like any other schema in the database according to the SQL specification while accessing data stored either in a different database or a different server instance. The import can be made either as an entire foreign schema or merely certain tables belonging to that foreign schema. While support for ISO/IEC 9075-9 bridges the gap between the two competing philosophies surrounding schemas, MySQL and Informix maintain an implicit association between databases while ISO/IEC 9075-9 requires that any such linkages be explicit in nature.


See also

*
Relational database management system A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relati ...
(includes market share data) * List of relational database management systems *
Comparison of object–relational database management systems This is a comparison of object–relational database management systems (ORDBMSs). Each system has at least some features of an object–relational database; they vary widely in their completeness and the approaches taken. The following tables c ...
* Comparison of database administration tools *
Object database An object database or object-oriented database is a database management system in which information is represented in the form of objects as used in object-oriented programming. Object databases are different from relational databases which a ...
– some of which have relational (SQL/ODBC) interfaces. * IBM Business System 12 – an historical
RDBMS A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
and related query language.


References


External links


Comparison of different SQL implementations against SQL standards
Includes Oracle, Db2, Microsoft SQL Server, MySQL and PostgreSQL. (8 June 2007)
The SQL92 standard


{{DEFAULTSORT:Relational database management systems
Relational databases A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relatio ...
Relational database management systems