Jump to content

Comparison of relational database management systems

From Wikipedia, the free encyclopedia

This is an old revision of this page, as edited by Mtasic (talk | contribs) at 16:20, 22 February 2009 (→Fundamental features). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

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 SQL Proprietary
ADABAS Software AG 1970????1970 8.1 Proprietary
Adaptive Server Enterprise Sybase 1987????1987 15.0 Proprietary
Advantage Database Server Sybase 1992????1992 8.1 Proprietary
Altibase Altibase Corp. 200007??July 2000 5.1.1 Proprietary
Apache Derby Apache 2004????2004 10.4.2.0 Apache License
Asql Marko Tasic 2009????2009 0.9.x Public domain
Datacom CA, Inc. ? 11.2 Proprietary
DB2 IBM 1982????1982 9.5 Proprietary
DBISAM Elevate Software ????????? 4.25 Proprietary
Datawasp Significant Data Systems 200804??April 2008 1.0.1 Proprietary
ElevateDB Elevate Software ????????? 1.01 Proprietary
FileMaker FileMaker 1984???? 1984 9 proprietary
Firebird Firebird project 20000725July 25, 2000 2.1.1 IPL and IDPL
Informix IBM 1985????1985 11.50 Proprietary
HSQLDB HSQL Development Group 2001????2001 1.8.0 BSD
H2 H2 Software 2005????2005 1.1.107 EPL and modified MPL
Ingres Ingres Corp. 1974????1974 Ingres Database 9.2.0 (November 2008) GPL and proprietary
InterBase CodeGear 1985????1985 2007 Proprietary
LucidDB The Eigenbase Project 200701??January 2007 0.7.4 GPL
MaxDB SAP AG ????????? 7.6 GPL or 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 4.16 (Feb. 2007) MonetDB Public License v1.1
MySQL Sun Microsystems 199611??November 1996 5.1.30 GPL or proprietary
HP NonStop SQL Hewlett-Packard 1987????1987 SQL MX 2.0 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 1 (September 2007) 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 ????????? 9 Proprietary
Polyhedra DBMS ENEA AB 1993????1993 8.0 (July 2008) Proprietary
PostgreSQL PostgreSQL Global Development Group 198906??June 1989 8.3.5 (2 November 2008) BSD
Pyrrho DBMS University of Paisley 200511??November 2005 0.5 Proprietary
RBase RBase ????????? 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 Freeware
SmallSQL SmallSQL 20050416April 16 2005 0.19 LGPL
SQL Anywhere Sybase 1992????1992 11.0 Proprietary
SQLite D. Richard Hipp 20000817August 17 2000 3.6.7 (December 2008) 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
Asql Yes Yes Yes Yes Yes Yes Yes Maybe
DataCom No No No No No No No Yes
Datawasp Yes No No No No No No
DB2 5 Yes No Yes No Yes No No Yes
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 Yes Yes Yes Yes Yes No No No
Ingres Yes Yes Yes Yes Yes No No Partial
InterBase Yes Yes Yes No Yes (Solaris) 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
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
Polyhedra DBMS Yes No Yes No Yes No No No
PostgreSQL Yes Yes Yes Yes Yes Yes No No
Pyrrho DBMS Yes (.NET) No Yes (Mono) No No No No No
RBase 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
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 10g on PowerPC. 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.

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
Asql Partial No Partial Yes API
Datawasp No Yes Yes Yes GUI
DB2 Yes Yes Yes Yes GUI & SQL
Firebird Yes Yes Yes Yes SQL
HSQLDB No 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
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 1 Yes 1 Yes 1 Partial SQL
OpenBase SQL Yes Yes Yes Yes GUI & SQL
Oracle Yes Yes Yes Yes SQL
Oracle Rdb Yes Yes Yes Yes ?
OpenEdge Yes No 2 Yes Yes OpenEdge ABL & SQL
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 ?
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
SQLite Yes No 3 Basic 3 Yes SQL
Teradata Yes Yes Yes Yes SQL
Valentina No Yes No Yes ?

Note (1): 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 (2): FOREIGN KEY constraints are parsed but are not enforced. Triggers can be used instead. Nested transactions are not supported. [1]

Note (3): Available via Triggers.

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
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 ? ?
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 0001 9999
Firebird Unlimited 1 ~32 TB 65,536 B Depends on data types used. 2 GB 32,767 B 64 bits 100 32768
Ingres Unlimited Unlimited 256 KB 1024 2 GB 32,000 B 64 bits 0001 9999
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 Unlimited 2 GB 65,500 B 255 2 GB 16 MB 32 bits 0001 9999
Microsoft SQL Server (does not include 2008) 524,258 TB (32,767 files * 16 TB max file size) 524,258 TB Unlimited 1024 2 GB 8000 B 64 bits 1753 2 9999
MySQL 5 Unlimited 2 GB (Win32 FAT32) to 16 TB (Solaris) 64 KB 3398 4 GB (longtext, longblob) 64 KB (text) 64 bits 1000 9999
Oracle Unlimited (4 GB * block size per tablespace) 4 GB * block size (with BIGFILE tablespace) Unlimited 1000 Unlimited 4000 B 126 bits -4712 9999
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 -4713 5874897
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) ? ? 2000 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): SQL Server 2008 will have minimum date of 0001-01-01[3]

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
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
LucidDB No 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
OpenBase SQL Yes Yes
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.[4]

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. [5].

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

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

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
DB2 No ? Yes No Yes Yes 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 Yes Yes Yes Yes Yes Yes Yes Yes
Ingres Yes Yes Ingres r4 No No Ingres r4 No No
InterBase 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 EE edition only 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
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 Yes 9 Yes Yes No No

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

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. [9]

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.4. Version 8.2 supports a related technique known as "in-memory bitmap scans".

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

Database capabilities

Union Intersect Except Inner joins Outer joins Inner selects Merge joins Blobs and Clobs
4th Dimension Yes ? ? Yes Yes No No Yes
ADABAS ? ? ? ? ? ? ? ?
Adaptive Server Enterprise Yes ? ? Yes Yes Yes No Yes
Advantage Database Server Yes ? ? Yes Yes Yes Yes Yes
Altibase 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 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
LucidDB Yes Yes Yes Yes Yes Yes Yes No
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 (2005 and beyond) Yes (2005 and beyond) Yes Yes Yes Yes Yes
MonetDB ? ? ? ? ? ? ? ?
MySQL Yes No No Yes Yes Yes Yes Yes
OpenBase SQL No No No Yes Yes Yes Yes Yes
Oracle Yes Yes Yes, via MINUS 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 Yes Yes No ? ? Yes
PostgreSQL Yes Yes Yes Yes Yes Yes Yes Yes
Pyrrho DBMS ? ? ? ? ? ? ? ?
ScimoreDB Yes ? ? Yes LEFT only Yes Yes Yes
SmallSQL ? ? ? ? ? ? ? ?
SQL Anywhere Yes Yes Yes Yes Yes Yes Yes Yes
SQLite Yes Yes Yes Yes Yes Yes ? Yes
Teradata Yes ? ? Yes Yes Yes Yes Yes
Valentina Yes ? ? Yes Yes Yes Yes Yes

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? ?
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
DB2 Yes, via CHECK CONSTRAINT 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
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
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 (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 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 ? ?
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 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 ? ?
PostgreSQL Yes 1 Yes 1 Yes 1 Yes 1 ? ?
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 (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. [10]

Access Control

Information about access control functionalities. (work in progress).

Native network encryption Brute-force protection Enterprise directory compatibility Password complexity rules Patch access Run unprivileged Audit Resource limit Separation of duties (between administrator, operator, backup, ... like RBAC) Security Certification
DB2 Yes ? Yes (LDAP, Kerberos, ...) Yes ? Yes Yes Yes Yes Yes (EAL4+ 1)
Firebird No 2 Yes [11] No Yes Partial (no security page) No No No No 3 ?
MySQL Yes (SSL with 4.0) No No ? Partial (no security page)[12] Yes ? ? ? 4 No
OpenBase SQL Yes ? Yes (Open Directory, LDAP) No ? ? ? ? ? ?
Microsoft SQL Yes ? Yes (Microsoft Active Directory) Yes ? ? Yes (From 2008) Yes Yes Yes (EAL4+ 1)
Oracle Yes Yes Yes Yes ? ? Yes Yes ? Yes (EAL4+ 1)
PostgreSQL Yes (SSL with 7.4) No Yes (LDAP, Kerberos, ... 5) ? Yes [13] Yes ? ? ? 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) Yes No No No No No
Sybase ASE Yes (optional; to pay) ? Yes (optional ?) Yes Partial (need to register; depend on which product) [14] Yes Yes Yes Yes Yes (EAL4+ 1)

Note (1): Common Criteria certified product list

Note (2): FirebirdSQL can use tunneling to protect network protection as any other network application [15].

Note (3): FirebirdSQL seems to only have SYSDBA user and DB owner. There is no separate roles for backup operator, security administrator.

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

Note (5): See manual Authentication methods

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.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 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 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. p. 300. ISBN 9780072260939.
Retrieved from "https://en.wikipedia.org/enwiki/w/index.php?title=Comparison_of_relational_database_management_systems&oldid=272517145"