Comparison of relational database management systems

From Seo Wiki - Search Engine Optimization and Programming Languages
Jump to navigationJump to search

Template:ProgLangCompare 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.

General information

Maintainer First public release date Latest stable version Software license
4th Dimension 4D s.a.s 1984????1984 v11.5 SQL Proprietary
ADABAS Software AG 1970????1970 8.1 Proprietary
Adaptive Server Enterprise Sybase 1987????1987 15.0 Proprietary
Advantage Database Server (ADS) Sybase 1992????1992 9.1 Proprietary
Altibase Altibase Corp. 200007??July 2000 5.1.1 Proprietary
Apache Derby Apache 2004????2004 10.5.3.0 (August 2009) Apache License
Datacom CA, Inc. ? 11.2 Proprietary
CUBRID Search Solution 2008????2008 CUBRID 2008 R2.1 (December 2009) BSD, GPL v2
DB2 IBM 1983????1982 9.7 Proprietary
Drizzle Brian Aker 2008????Mid-2008 Build 1126 BSD, GPL v2
EffiProz EffiProz 1984???? 2009 0.1.0 (Dec 2009) Ms-PL
FileMaker FileMaker 1984???? 1984 10.0v1 (Jan 2009) Proprietary
Firebird Firebird project 20000725July 25, 2000 2.1.3 (September 2009) IPL and IDPL
FrontBase FrontBase, Inc 1996????1996 5.1.2 (January 2010) Proprietary
HSQLDB HSQL Development Group 2001????2001 1.8.1.1 (September 2009) BSD
H2 H2 Software 2005????2005 1.1.116 EPL and modified MPL
Informix Dynamic Server IBM 1981????1985 11.50.xC6 (December 2009) Proprietary
Ingres Ingres Corp. 1974????1974 Ingres Database 9.2 (December 2008) GPL and Proprietary
Linter SQL RDBMS RELEX Group 1990????1990 6.1 Proprietary
LucidDB The Eigenbase Project 200701??January 2007 0.7.4 GPL v2
MaxDB SAP AG ????????? 7.6 (January 2008) Proprietary
Microsoft Access Microsoft 1992????1992 12 (2007) Proprietary
Microsoft Visual Foxpro Microsoft ????????? 9 (2005) Proprietary
Microsoft SQL Server Microsoft 1989????1989 2008 (v10.0) Proprietary
MonetDB The MonetDB Developer Team 2004????2004 5.6 (June 2008) MonetDB Public License v1.1
mSQL Hughes Technologies 1994??1994 3.8 (9 June 2006) Proprietary
MySQL Sun Microsystems 199511??November 1995 5.1.42 (15 December 2009) GPL or Proprietary
Nexusdb Nexus Database Systems Pty Ltd NexusDB History ? Proprietary
HP NonStop SQL Hewlett-Packard 1987????1987 SQL/MX 2.3 Proprietary
Omnis Studio TigerLogic Inc 198207??July 1982 4.3.1 Release 1 (May 2008) Proprietary
OpenBase SQL OpenBase International 1991????1991 11.0.0 Proprietary
Oracle Oracle Corporation 197911??November 1979 11g Release 2 (September 2009) Proprietary
Oracle Rdb Oracle Corporation 1984????1984 7.2 Proprietary
OpenEdge Progress Software Corporation 1984????1984 10.1C Proprietary
OpenLink Virtuoso OpenLink Software 1998????1998 5.0.5 (January 2008) GPL or Proprietary
Pervasive PSQL Pervasive Software 1982????1982 10 Proprietary
Polyhedra DBMS ENEA AB 1993????1993 8.2 (July 2009) Proprietary
PostgreSQL PostgreSQL Global Development Group 198906??June 1989 8.4.2 (14 December 2009) BSD
Postgres Plus Standard Server EnterpriseDB 198906??2004 8.3 BSD
Postgres Plus Advanced Server EnterpriseDB 198906??2004 8.3 R2 ?
R:Base R:BASE Technologies 1982????1982 7.6 Proprietary
RDM Embedded Birdstep Technology 1984????1984 8.1 Proprietary
RDM Server Birdstep Technology 1990????1990 8.0 Proprietary
ScimoreDB Scimore 2005????2005 2.5 (March 2008) Proprietary
SmallSQL SmallSQL 20050416April 16, 2005 0.20 (December 2008) LGPL
SQL Anywhere Sybase 1992????1992 11.0 Proprietary
SQLBase Unify Corp. 19821982 11.5 (November 2008) Proprietary
SQLite D. Richard Hipp 20000817August 17, 2000 3.6.22 (6 January 2010) Public domain
Superbase Superbase 1984????1984 Scientific (2004) Proprietary
Teradata Teradata 1984????1984 V12 Proprietary
Valentina Paradigma Software 199802??February 1998 3.0.1 Proprietary

Operating system support

The operating systems the RDBMSes can run on.

Windows Mac OS X Linux BSD UNIX AmigaOS Symbian z/OS 1
4th Dimension Yes Yes No No No No No No
ADABAS Yes No Yes No Yes No No Yes
Adaptive Server Enterprise Yes No Yes Yes Yes No No No
Advantage Database Server Yes No Yes No No No No No
Altibase Yes No Yes No Yes No No No
Apache Derby 2 Yes Yes Yes Yes Yes No No Yes
CUBRID Yes No Yes No No No No No
DB2 5 Yes No Yes No Yes No No Yes
EffiProz Yes No No No No No No No
Firebird Yes Yes Yes Yes Yes No No Maybe
HSQLDB 2 Yes Yes Yes Yes Yes No No Yes
H2 2 Yes Yes Yes Yes Yes No No Maybe
FileMaker Yes Yes No No No No No No
Informix Dynamic Server Yes Yes Yes Yes Yes No No Yes
Ingres Yes Yes Yes Yes Yes No No Partial
InterBase Yes Yes Yes No Yes (Solaris) No No No
Linter SQL RDBMS 6 Yes Yes Yes Yes Yes6 No No No
LucidDB Yes No Yes No No No No No
MaxDB Yes No Yes No Yes No No Maybe
Microsoft Access Yes No No No No No No No
Microsoft Visual Foxpro Yes No No No No No No No
Microsoft SQL Server Yes No No No No No No No
MonetDB Yes Yes Yes No Yes No No No
MySQL Yes Yes Yes Yes Yes Yes Yes Maybe
Omnis Studio Yes Yes Yes No No No No No
OpenBase SQL Yes Yes Yes Yes Yes No No No
Oracle 4 Yes Yes Yes No Yes No No Yes
Oracle Rdb 3 No No No No No No No
OpenEdge Yes No Yes No Yes No No No
OpenLink Virtuoso Yes Yes Yes Yes Yes No No Yes
Pervasive PSQL Yes Yes (OEM only) Yes No No No No No
Polyhedra 7 Yes No Yes No Yes No No No
PostgreSQL Yes Yes Yes Yes Yes Yes No No
Postgres Plus Standard Server Yes Yes Yes Yes Yes Yes No No
Postgres Plus Advanced Server Yes Yes Yes Yes Yes Yes No No
R:Base Yes No No No No No No No
RDM Embedded Yes Yes Yes Yes Yes No No No
RDM Server Yes Yes Yes Yes Yes No No No
ScimoreDB Yes No No No No No No No
SmallSQL 2 Yes Yes Yes Yes Yes No No Yes
SQL Anywhere Yes Yes Yes No Yes No No No
SQLBase Yes No Yes No No No No No
SQLite Yes Yes Yes Yes Yes Yes Yes Maybe
Superbase Yes No No No No Yes No No
Teradata Yes No Yes No Yes No No No
Valentina Yes Yes Yes No No 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. Mac OS X is limited to 10gR2. 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.

Note (6): Linter SQL RDBMS also runs on OpenVMS, Solaris, QNX, OS9000 and OS9.

Note (7): Polyhedra also runs on AIX, OSE, Solaris, LynxOS and VxWorks. Previous versions also ran on Ultrix, VMS and pSOS. Source code kits allow customers to port to other platforms.

Fundamental features

Information about what fundamental RDBMS features are implemented natively.

ACID Referential integrity Transactions Unicode Interface
4th Dimension Yes Yes Yes Yes GUI & SQL
ADABAS ? ? ? ? ?
Adaptive Server Enterprise Yes Yes Yes Yes SQL
Advantage Database Server Yes Yes Yes No API & SQL
Altibase Yes Yes Yes ? SQL
Apache Derby Yes Yes Yes Yes SQL
CUBRID Yes Yes Yes Yes GUI & SQL
DB2 Yes Yes Yes Yes GUI & SQL
EffiProz Yes Yes Yes Yes SQL
Firebird Yes Yes Yes Yes SQL
HSQLDB No 1 Yes Yes Yes SQL
H2 Yes Yes Yes Yes SQL
Informix Dynamic Server Yes Yes Yes Yes SQL
Ingres Yes Yes Yes Yes SQL & QUEL
InterBase Yes Yes Yes Yes SQL
Linter SQL RDBMS Yes Yes Yes Yes GUI & SQL
LucidDB Yes No No No 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 GUI & SQL
MonetDB Yes Yes Yes Yes ?
MySQL Yes 2 Yes 2 Yes 2 Partial SQL
OpenBase SQL Yes Yes Yes Yes GUI & SQL
Oracle Yes Yes Yes Yes GUI & SQL
Oracle Rdb Yes Yes Yes Yes SQL
OpenLink Virtuoso Yes Yes Yes Yes ?
Polyhedra DBMS Yes Yes Yes Yes SQL
PostgreSQL Yes Yes Yes Yes SQL
Postgres Plus Standard Server Yes Yes Yes Yes SQL
Postgres Plus Advanced Server Yes Yes Yes Yes SQL
RDM Embedded Yes Yes Yes Yes SQL & API
RDM Server Yes Yes Yes Yes SQL & API
ScimoreDB Yes Yes Yes Partial SQL
SQL Anywhere Yes Yes Yes Yes SQL
SQLBase Yes Yes Yes Yes API & GUI & SQL
SQLite Yes Yes Yes Yes SQL
Teradata Yes Yes Yes Yes SQL
Valentina No Yes No Yes ?
ACID Referential integrity Transactions Unicode Interface

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): 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.

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 Min DATE value Max DATE value Max column name size
4th Dimension Unlimited ? ? 65135 200 GB (2 GiB Unicode) 200 GB (2 GiB Unicode) 64 bits ? ? ?
Advantage Database Server Unlimited 16 EB (16 EiB) 65530 B 65135/(10+AverageFieldNameLength) 4 GB (4 GiB) ? 64 bits ? ? ?
Apache Derby ? ? ? 1012 (5000 in views) 2,147,483,647 chars 254 (VARCHAR: 32672) ? 0001-01-01 9999-12-31 128
CUBRID 2 EB 2 EB ? 6400 1 GB (GLO type supported) 1 GB 64 bits 0001 9999 254
DB2 512 TB (512 TiB) 512 TB 32,677 B 1012 2 GB 32 KB (32 KiB) 64 bits 0001 9999 128
Firebird Unlimited 1 ~32 TB 65,536 B Depends on data types used. 2 GB 32,767 B 64 bits 100 32768 31
H2 256 GB (256 GiB) 7 Unlimited 8 Unlimited 8 Unlimited 8 256 GB (256 GiB) Unlimited 8 64 bits ? ? Unlimited 8
Informix Dynamic Server ~128PB ~128PB 32765 bytes (exclusive of large objects) 32765 4TB 32765 10^32 12/31/1900 12/31/9999 128 bytes
Ingres Unlimited Unlimited 256 KB 1024 2 GB 32,000 B 64 bits 0001 9999 32
Linter SQL RDBMS Unlimited 2^30 rows 64KB (w/o BLOBs), 4GB (BLOB) 250 4GB 4KB 64 bits 0001-01-01 2099-12-31 128
Microsoft Access 2 GB 2 GB 16 MB 255 64 KB (memo field), 1 GB ("OLE Object" field) 255 B (text field) 32 bits 0100 9999 ?
Microsoft Visual Foxpro Unlimited 2 GB 65,500 B 255 2 GB 16 MB 32 bits 0001 9999 ?
Microsoft SQL Server 524,258 TB (32,767 files * 16 TB max file size) 524,258 TB Unlimited 30000 2 GB 2 GB 6 126 bits 2 0001 9999 128
MySQL 5 Unlimited MyISAM storage limits: 256TB; Innodb storage limits: 64TB 64 KB 3 4096 4 4 GB (longtext, longblob) 64 KB (text) 64 bits 1000 9999 64
Oracle Unlimited (4 GB * block size per tablespace) 4 GB * block size (with BIGFILE tablespace) 8KB 1000 Unlimited 4000 B 126 bits -4712 9999 30
Polyhedra Limited only by available RAM, address space 232 rows Unlimited 65536 4 GB (subject to RAM) 4 GB (subject to RAM) 32 bits 0001-01-01 8000-12-31 255
PostgreSQL Unlimited 32 TB 1.6 TB 250-1600 depending on type 1 GB (text, bytea) - stored inline or 2 GB (stored in pg_largeobject) 1 GB Unlimited -4713 5874897 63
Postgres Standard Server Unlimited 32 TB 1.6 TB 250-1600 depending on type 1 GB (text, bytea) - stored inline or 2 GB (stored in pg_largeobject) 1 GB Unlimited -4713 5874897 63
Postgres Advanced Server Unlimited 32 TB 1.6 TB 250-1600 depending on type 1 GB (text, bytea) - stored inline or 2 GB (stored in pg_largeobject) 1 GB Unlimited -4713 5874897 63
ScimoreDB Unlimited 16 EB 8050 B 255 16 TB 8000 B 64 bits ? ? ?
SQL Anywhere 104 TB (13 files, each file up to 8 TB (32k pages)) Limited by file size Limited by file size 45000 2 GB 2 GB 64 bits 0001-01-01 9999-12-31 ?
SQLite 32 TB (230 pages * 32 KB max page size) ? ? 32767 1 GB 1 GB 64 bits No DATE type No DATE type ?
Teradata Unlimited Unlimited 64 KB wo/lobs (64 GB w/lobs) 2048 2 GB 10,000 64 bits ? 9999-12-31 Select 80991231 (date); ?

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.

Note (2): limit is 1038using DECIMAL datatype[3]

Note (3): InnoDB is limited to 8000 bytes (excluding VARBINARY, VARCHAR, BLOB, or TEXT columns) [4]

Note (4): InnoDB is limited to 1000 columns[5]

Note (6): Using VARCHAR(MAX) in SQL 2005 and later

Note (7): H2 database size limits do not include BLOB or CLOB objects, which are stored separately.

Note (8): Java array size limit of 2,147,483,648 (2^31) objects per array applies. This limit applies to: number of characters in names, rows per table, columns per table, and characters per CHAR/VARCHAR.

Tables and views

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

Temporary table Materialized view
4th Dimension Yes Planned for inclusion in next major release
ADABAS ? ?
Adaptive Server Enterprise Yes  1 No
Advantage Database Server Yes No (only common views)
Altibase Yes Yes
Apache Derby Yes No
CUBRID No No
DB2 Yes Yes
EffiProz Yes No
Firebird Yes No (only common views)
HSQLDB Yes No
H2 Yes No
Informix Dynamic Server Yes No 2
Ingres Yes Planned for inclusion in next major release
InterBase Yes No
Linter SQL RDBMS Yes No
LucidDB No No
MaxDB Yes No
Microsoft Access Yes No
Microsoft Visual Foxpro Yes Yes
Microsoft SQL Server Yes Yes 3
MonetDB Yes No
MySQL Yes No 4
OpenBase SQL Yes Yes
Oracle Yes Yes
Oracle Rdb Yes Yes
OpenLink Virtuoso Yes Yes
Polyhedra DBMS No No (only common views)
PostgreSQL Yes No 5
Postgres Standard Server Yes No 5
Postgres Advanced Server Yes No 5
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.[6]

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 you cannot for example define the way it is refreshed or build an index on it. The term is defined in the Informix Performance Guide [7].

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

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

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

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 ? Cluster ? ? ? ? ? ?
ADABAS ? ? ? ? ? ? ? ?
Adaptive Server Enterprise No No No No Yes No No No
Apache Derby No No No No No No No No
CUBRID No No No No Yes No No No
DB2 No ? Yes No Yes Yes No No
EffiProz No No No No No No No No
Firebird No No Yes No Yes 1 No No No
HSQLDB No No No No No No No No
H2 No Yes No No No No No No
Informix Dynamic Server Yes Yes Yes Yes Yes Yes Yes Yes
Ingres Yes Yes Ingres v10 No No Ingres v10 No No
InterBase No No No No No No No No
Linter SQL RDBMS 10 No No No No No No No No
LucidDB No No No No No Yes 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 2 Yes No No
Microsoft SQL Server ? Non/Cluster & fill factor Yes 3 Yes 4 No 3 No No No
MonetDB No Yes No No No No No No
MySQL MyISAM tables only MEMORY, Cluster (NDB), InnoDB,5 tables only No No No No No No
Oracle Yes 11 Cluster Tables Yes Yes 6 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 7 Yes 8 Yes Yes
Postgres Standard Server Yes Yes Yes Yes Yes 7 Yes 8 Yes Yes
Postgres Advanced Server Yes Yes Yes Yes Yes 7 Yes 8 Yes Yes
ScimoreDB No No No No No No No No
SQL Anywhere No No No No No No No No
SQLite Yes No No No Yes No No No
Teradata No Yes Yes Yes No Yes No No
Valentina No No Yes Yes 9 Yes Yes No No

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

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[1])

Note (4): Can be implemented by using an indexed view. [12]

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 (8): PostgreSQL will likely support on-disk bitmap indexes in 8.5. Version 8.2 supports a related technique known as "in-memory bitmap scans".

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

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

Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs Common Table Expressions Windowing Functions
4th Dimension Yes ? ? Yes Yes No No Yes ? ?
ADABAS Yes ? ? ? ? ? ? ? ? ?
Adaptive Server Enterprise Yes ? ? Yes Yes Yes Yes Yes ? ?
Advantage Database Server Yes ? ? Yes Yes Yes Yes Yes ? ?
Altibase Yes ? ? Yes Yes Yes Yes Yes ? ?
Apache Derby Yes Yes Yes Yes Yes ? ? Yes No No
CUBRID Yes Yes Yes Yes Yes Yes Yes Yes No No
DB2 Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
EffiProz Yes Yes Yes Yes Yes Yes No Yes No No
Firebird Yes ? ? Yes Yes Yes Yes Yes Yes ?
Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs Common Table Expressions Windowing Functions
HSQLDB Yes Yes Yes Yes Yes ? ? ? ? ?
H2 Yes Yes Yes Yes Yes Yes No Yes No Yes
Informix Dynamic Server Yes ? Yes, via MINUS Yes Yes Yes Yes Yes ? ?
Ingres Yes No No Yes Yes Yes Yes Yes No No
InterBase Yes ? ? Yes Yes ? ? Yes ? ?
Linter SQL RDBMS Yes Yes Yes Yes Yes Yes Yes Yes No Yes
LucidDB Yes Yes Yes Yes Yes Yes Yes No ? ?
MaxDB Yes ? ? Yes Yes Yes No Yes ? ?
Microsoft Access Yes No No Yes Yes Yes No Yes No No
Microsoft Visual Foxpro Yes ? ? Yes Yes Yes ? Yes ? ?
Microsoft SQL Server Yes Yes (2005 and beyond) Yes (2005 and beyond) Yes Yes Yes Yes Yes Yes Yes
Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs Common Table Expressions Windowing Functions
MonetDB ? ? ? ? ? ? ? ? ? ?
MySQL Yes No No Yes Yes Yes Yes Yes No No
OpenBase SQL No No No Yes Yes Yes Yes Yes ? ?
Oracle Yes Yes Yes, via MINUS Yes Yes Yes Yes Yes Yes. Recursive CTEs introduced in 11gR2 supersedes similar construct called CONNECT BY Yes
Oracle Rdb Yes ? ? Yes Yes Yes Yes Yes ? ?
OpenLink Virtuoso Yes ? ? Yes Yes Yes ? Yes ? ?
Polyhedra DBMS Yes Yes Yes Yes No ? ? Yes ? ?
PostgreSQL Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Postgres Plus Standard Server Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Postgres Plus Advanced Server Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes
Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs Common Table Expressions Windowing Functions
ScimoreDB Yes ? ? Yes LEFT only Yes Yes Yes ? ?
SmallSQL ? ? ? ? ? ? ? ? ? ?
SQL Anywhere Yes Yes Yes Yes Yes Yes Yes Yes ? ?
SQLite Yes Yes Yes Yes LEFT only Yes ? Yes ? ?
Teradata Yes ? ? Yes Yes Yes Yes Yes ? Yes
Valentina Yes ? ? Yes Yes Yes Yes Yes ? ?

Data types

Type system Integer Floating point Decimal String Binary Date/Time Boolean Other
CUBRID[2] Static SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) FLOAT, REAL(32-bit), DOUBLE(64-bit) DECIMAL, NUMERIC CHAR, VARCHAR, NCHAR, NVARCHAR GLO DATE, DATETIME, TIME, TIMESTAMP N/A MONETARY, BIT, BIT VARYING, TABLE, SET, MULTISET, SEQUENCE
Informix Dynamic Server[3] Static SMALLINT (16-bit), INT (32-bit), INT8 (64-bit proprietary), BIGINT (64-bit) SMALLFLOAT (32-bit), FLOAT (64-bit) DECIMAL (32 digits float/fixed), MONEY CHAR, VARCHAR, NCHAR, NVARCHAR, LVARCHAR TEXT, BYTE, BLOB, CLOB DATE, DATETIME, INTERVAL BOOLEAN SET, LIST, MULTISET, ROW, USER DEFINED TYPES
Ingres[4] Static TINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) FLOAT4 (32-bit), FLOAT (64-bit) DECIMAL C, CHAR, VARCHAR, LONG VARCHAR, NCHAR, NVARCHAR, LONG NVARCHAR, TEXT BYTE, VARBYTE, LONG VARBYTE (BLOB) DATE, ANSIDATE, INGRESDATE, TIME, TIMESTAMP, INTERVAL N/A MONEY, OBJECT_KEY, TABLE_KEY, USER-DEFINED DATA TYPES (via OME)
MySQL[5] Static TINYINT (8-bit), SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit) FLOAT (32-bit), DOUBLE (aka REAL) (64-bit) DECIMAL CHAR, VARCHAR, TEXT BLOB DATETIME, DATE, TIMESTAMP, YEAR BOOLEAN (aka BOOL) = synonym for TINYINT ENUM, SET
Oracle[6] Static + Dynamic (through ANYDATA) NUMBER BINARY_FLOAT, BINARY_DOUBLE NUMBER CHAR, VARCHAR2, CLOB, NCLOB, NVARCHAR2, NCHAR BLOB, RAW, LONGRAW, BFILE DATE, TIMESTAMP (with/without TIMEZONE), INTERVAL N/A SPATIAL, IMAGE, AUDIO, VIDEO, DICOM, XMLType
Polyhedra Static INTEGER8 (8-bit), INTEGER(16-bit), INTEGER (32-bit) FLOAT32 (32-bit), FLOAT (aka REAL; 64-bit) N/A VARCHAR, LARGE VARCHAR (aka CHARACTER LARGE OBJECT) LARGE BINARY (aka BINARY LARGE OBJECT) DATETIME BOOLEAN N/A
PostgreSQL[7] Static SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit) REAL (32-bit), DOUBLE PRECISION (64-bit) DECIMAL, NUMERIC CHAR, VARCHAR, TEXT BYTEA DATE, TIME, TIMESTAMP, INTERVAL BOOLEAN ENUM, POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE, CIDR, INET, MACADDR, BIT, UUID, XML, arrays
SQL Server[8] Static TINYINT, SMALLINT, INT, BIGINT FLOAT, REAL NUMERIC, DECIMAL, SMALLMONEY, MONEY CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT BINARY, VARBINARY, IMAGE, FILESTREAM DATE, DATETIMEOFFSET, DATETIME2, SMALLDATETIME, DATETIME, TIME BIT CURSOR, TIMESTAMP, HIERARCHYID, UNIQUEIDENTIFIER, SQL_VARIANT, XML, TABLE
SQLite[9] Dynamic INTEGER (64-bit) REAL (aka FLOAT, DOUBLE) (64-bit) N/A TEXT (aka CHAR, CLOB) BLOB N/A N/A

Other objects

Information about what other objects are supported natively.

Data Domain Cursor Trigger Function 1 Procedure 1 External routine 1
4th Dimension Yes No Yes Yes Yes Yes
ADABAS ? Yes ? 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 2 Yes 2 Yes 2
CUBRID Yes Yes Yes Yes No Yes
DB2 Yes, via CHECK CONSTRAINT Yes Yes Yes Yes Yes
EffiProz Yes No Yes Yes Yes No
Firebird Yes Yes Yes Yes Yes Yes
HSQLDB ? No Yes Yes Yes Yes
H2 Yes No Yes 2 Yes 2 Yes 2 Yes
Informix Dynamic Server Template:Yes via CHECK Yes Yes Yes Yes Yes
Ingres Yes Yes Yes Yes Yes Yes
InterBase Yes Yes Yes Yes Yes Yes
Linter SQL RDBMS No Yes Yes Yes Yes No
LucidDB No Yes No Yes 2 Yes 2 Yes 2
MaxDB Yes Yes Yes Yes Yes ?
Microsoft Access Yes 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
OpenBase SQL 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
Postgres Standard Server Yes Yes Yes Yes Yes Yes
Postgres Advanced Server 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 (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" is a commonly used term for these routine types. However, its definition varies between different database vendors.

Note (2): In Derby, H2, and LucidDB, users code functions and procedures in Java.

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 ? ?
CUBRID Yes Yes No Yes No ?
IBM DB2 Yes Yes Yes Yes ? ?
EffiProz No No No No No No
Firebird No No No No Yes No
HSQLDB ? ? ? ? ? ?
H2 No No No No No No
Informix Dynamic Server Yes Yes Yes Yes ? ?
Ingres Yes Yes Yes Yes No No
InterBase No No No No Yes Yes
Linter SQL RDBMS No No No No No No
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 Yes Yes Yes ? ?
OpenBase SQL ? ? ? ? ? ?
Oracle Yes Yes Yes Yes ? ?
Oracle Rdb Yes Yes ? ? ? ?
OpenLink Virtuoso Yes No No No ? ?
Polyhedra DBMS No No No No No No
PostgreSQL Yes 1 Yes 1 Yes 1 Yes 1 ? ?
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 (1): PostgreSQL 8.1 provides partitioning support through check constraints. Range, List and Hash methods can be emulated with PL/pgSQL or other procedural languages. [13]

Access Control

Information about access control functionalities (work in progress).

Native network encryption 1 Brute-force protection Enterprise directory compatibility Password complexity rules 2 Patch access 3 Run unprivileged 4 Audit Resource limit Separation of duties (between administrator, operator, backup, ... like RBAC) 5 Security Certification
DB2 Yes ? Yes (LDAP, Kerberos, ...) Yes ? Yes Yes Yes Yes Yes (EAL4+ 6)
Firebird No Yes [14] Yes (Windows trusted authenification) No Partial (no security page)[15] Yes No No No 7 ?
H2 Yes Yes ? No ? Yes ? Yes Yes No
Linter SQL RDBMS Yes (with SSL) Yes No Yes (length only) No Yes Yes Yes Yes Yes
MySQL Yes (SSL with 4.0) No No No Partial (no security page)[16] Yes ? ? ? 8 No
OpenBase SQL Yes ? Yes (Open Directory, LDAP) No ? ? ? ? ? ?
Microsoft SQL Server Yes ? Yes (Microsoft Active Directory) Yes Yes Yes Yes (From 2008) Yes Yes Yes (EAL1+ 1)
Oracle Yes Yes Yes Yes ? ? Yes Yes ? Yes (EAL4+ 1)
PostgreSQL Yes No Yes (LDAP, Kerberos, ... 9) No Yes [17] Yes No Yes No Yes (EAL1 1)
SQL Anywhere Yes ? Yes (Kerberos) Yes ? Yes Yes No Yes Yes (EAL3+ 1 as Adaptive Server Anywhere)
SQLite No (not relevant, only file permissions) No (not relevant) No (not relevant) No (not relevant) Partial (no security page)[18] Yes (file access) Yes Yes No No
Sybase ASE Yes (optional; to pay) ? Yes (optional ?) Yes Partial (need to register; depend on which product) [19] Yes Yes Yes Yes Yes (EAL4+ 1)

Note (1): Network traffic could be transmitted in a secure way (not clear-text, en 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 is no separate roles for backup operator and security administrator.

Note (8): User can define a dedicated backup user but nothing particular in default install [20]

Note (9): See manual Authentication methods

Databases vs Schemas (terminology)

The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly 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.table

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:

  • 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 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 applied more of the specification 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.

Oracle has its own spin where creating a user is synonymous with creating a schema. Thus a DBA 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 Oracle 'shared-everything' 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 server. In the Oracle view, the '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.

The end result is confusion between the database factions. The Postgres and Oracle communities maintain that generally one database is all that is needed for one project; and the MySQL proponents, 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 pragmatic counterargument allows their users to get the job done without any major drawback.

See also

References

  1. ↑ Petkovic, Dusan (2005). Microsoft SQL Server 2005: A Beginner's Guide. McGraw-Hill Professional. pp. 300. ISBN 9780072260939. 
  2. ↑ CUBRID Reference Manual, CUBRID SQL Guide, Data Types
  3. ↑ [1]
  4. ↑ Ingres SQL Reference Guide, Chapter 3: Understanding SQL Data Types
  5. ↑ MySQL 5.0 Reference Manual :: Chapter 10. Data Types
  6. ↑ Oracle 11g SQL Reference. Data Types
  7. ↑ PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Data Types
  8. ↑ SQL Server Data Types
  9. ↑ Datatypes In SQLite Version 3

External links

Search Wikibooks Wikibooks has a book on the topic of

Template:Databases

es:Anexo:Comparación de sistemas administradores de bases de datos relacionales hr:Usporedba sustava za upravljanje relacijskim bazama podataka ja:関係データベース管理システムの比較 vi:So sánh các hệ quản trị cơ sở dữ liệu quan hệ zh:关系型数据库管理系统比较

If you like SEOmastering Site, you can support it by - BTC: bc1qppjcl3c2cyjazy6lepmrv3fh6ke9mxs7zpfky0 , TRC20 and more...

→

Retrieved from "https://mail.seomastering.com:443/w/index.php?title=Comparison_of_relational_database_management_systems&oldid=9280"