The following tables compare general and technical information for a number ofrelational 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
[hide]- 1General information
- 2Timeline
- 3Operating system support
- 4Fundamental features
- 5Limits
- 6Tables and views
- 7Indexes
- 8Database capabilities
- 9Data types
- 10Other objects
- 11Partitioning
- 12Access control
- 13Databases vs schemas (terminology)
- 14See also
- 15References
- 16External links
[edit]General information
| Maintainer | First public release date | Latest stable version | Latest release date | Software license | 4D (4th Dimension) | 4D s.a.s1984v12.2 SQL ProprietaryADABAS | Software AG19708.1 ProprietaryAdaptive Server Enterprise | Sybase198715.5 ProprietaryAdvantage Database Server (ADS) | Sybase199210.12010-12ProprietaryAltibase | Altibase Corp.[1]2000-075.1.1 ProprietaryApache Derby | Apache200410.8.1.22011-05-02Apache LicenseCUBRID | NHN Corporation[2]2008-118.4.02011-05-12GPL v2Datacom | CA, Inc.?11.2 ProprietaryDB2 | IBM19839.72009-04-22ProprietaryDrizzle | Brian Aker2008Build 1126 BSD,GPL v2Empress Embedded Database | Empress Software Inc[3]197910.202010-03ProprietaryFileMaker | FileMaker[4]198411.02010-03ProprietaryFirebird | Firebird project[5]2000-07-252.52010-10-04IPL[6] and IDPL[7]FrontBase | FrontBase, Inc[8]19965.1.22010-01ProprietaryHSQLDB | HSQL Development Group20012.2.52011-07BSDH2 | H2 Software20051.3.1542011-04-04EPL and modifiedMPLInformix Dynamic Server | IBM198011.70.xC22011-03-28ProprietaryIngres | Ingres Corp.1974Ingres Database 102010-10-12GPL andProprietaryInterBase | Embarcadero1984InterBase XE2010-09-21ProprietaryLinter SQL RDBMS | RELEX Group19906.x ProprietaryLucidDB | The Eigenbase Project[9]2007-010.9.3 GPL v2MaxDB | SAP AG2003-057.62008-01ProprietaryMicrosoft Access (JET) | Microsoft199214 (2010) ProprietaryMicrosoft Visual Foxpro | Microsoft1984[10]9 (2005)2007-10-11[10]ProprietaryMicrosoft SQL Server | Microsoft19892008 R2 (v10.5) ProprietaryMicrosoft SQL Server Compact (Embedded Database) | Microsoft20002010 (v3.5 SP2) ProprietaryMonetDB | The MonetDB Developer Team20045.222010-10MonetDB Public License v1.1[11]mSQL | Hughes Technologies19943.8[12]2006-06-09ProprietaryMySQL | Sun Microsystems (nowOracle Corporation)1995-115.5.82010-12-15GPL orProprietaryNavibase | Naviworx20091.0.02010-11-02ProprietaryNexusdb | Nexus Database Systems Pty Ltd[13]2003-093.042010-05-08ProprietaryHP NonStop SQL | Hewlett-Packard1987SQL/MX 2.3 ProprietaryOmnis Studio | TigerLogic Inc[14]1982-074.3.1 Release 1no2008-05ProprietaryOpenBase SQL | OpenBase International[15]199111.0.0 ProprietaryOracle | Oracle Corporation1979-1111g Release 22009-09ProprietaryOracle Rdb | Oracle Corporation19847.2.5.02011-06-20[16]ProprietaryOpenEdge | Progress Software Corporation198410.2B ProprietaryOpenLink Virtuoso | OpenLink Software[17]19986.22010-09GPL orProprietaryParadox | Corel Corporation[18]1985112003ProprietaryPervasive PSQL | Pervasive Software1982112010-09ProprietaryPolyhedra DBMS | ENEA AB19938.42010-12ProprietaryPostgreSQL | PostgreSQL Global Development Group[19]1989-069.0.4[20]2011-04-18PostgreSQL licence (Free and Open Source)[21]R:Base | R:BASE Technologies[22]19827.6 ProprietaryRDM Embedded | Raima Inc.[23]198410 ProprietaryRDM Server | Raima Inc.[23]19908.3 ProprietaryScimoreDB | Scimore[24]20053.02008-03-03ProprietarySmallSQL | SmallSQL2005-04-160.202008-12LGPLSQL Anywhere | Sybase199212.02010-07-09ProprietarySQLBase | Unify Corp.[25]198211.52008-11ProprietarySQLite | D. Richard Hipp2000-08-173.7.6.32011-05-19[26]Public domainSuperbase | Superbase1984Scientific (2004) ProprietaryTeradata | Teradata198413.10 ProprietaryUniData | Rocket Software19887.2.102011-04ProprietaryUniVerse | Rocket Software198511.1.32011-06Proprietary[edit]Timeline
Timeline of the development of major RDBMS software:
[edit]Operating system support
The operating systems the RDBMSes can run on.
| Windows | Mac OS X | Linux | BSD | UNIX | AmigaOS | Symbian | z/OS1 | 4th Dimension | YesYesNoNoNoNoNoNoADABAS | YesNoYesNoYesNoNoYesAdaptive Server Enterprise | YesNoYesYesYesNoNoNoAdvantage Database Server | YesNoYesNoNoNoNoNoAltibase | YesNoYesNoYesNoNoNoApache Derby2 | YesYesYesYesYesNoNoYesCUBRID | YesPartial10YesNoNoNoNoNoDrizzle | NoYesYesYesYesNoNoNoDB25 | YesYes (Express C)YesNoYesNoNoYesEmpress Embedded Database | YesYesYesYesYesNoNoNoFirebird | YesYesYesYesYesNoNoMaybeHSQLDB2 | YesYesYesYesYesNoNoYesH22 | YesYesYesYesYesNoNoYesFileMaker | YesYesNoNoNoNoNoNoInformix Dynamic Server | YesYesYesYesYesNoNoYesIngres | YesYesYesYesYesNoNoPartialInterBase | YesYesYesNoYes (Solaris)NoNoNoLinter SQL RDBMS6 | YesYesYesYesYes6NoNoNoLucidDB | YesYesYesNoNoNoNoNoMaxDB | YesNoYesNoYesNoNoMaybeMicrosoft Access (JET) | YesNoNoNoNoNoNoNoMicrosoft Visual Foxpro | YesNoNoNoNoNoNoNoMicrosoft SQL Server | YesNoNoNoNoNoNoNoMicrosoft SQL Server Compact (Embedded Database) | YesNoNoNoNoNoNoNoMonetDB | YesYesYesNoYesNoNoNoMySQL8 | YesYesYesYesYesYesYesYesNavibase | PartialYesPartialPartialNoNoNoNoOmnis Studio | YesYesYesNoNoNoNoNoOpenBase SQL | YesYesYesYesYesNoNoNoOracle4 | YesYesYesNoYesNoNoYesOracle Rdb3 | NoNoNoNoNoNoNoOpenEdge | YesNoYesNoYesNoNoNoOpenLink Virtuoso | YesYesYesYesYesNoNoYesPervasive PSQL | YesYes (OEM only)YesNoNoNoNoNoPolyhedra7 | YesNoYesNoYesNoNoNoPostgreSQL | YesYesYesYesYesNoNoNoR:Base | YesNoNoNoNoNoNoNoRDM Embedded | YesYesYesYesYesNoNoNoRDM Server | YesYesYesYesYesNoNoNoScimoreDB | YesNoNoNoNoNoNoNoSmallSQL2 | YesYesYesYesYesNoNoYesSQL Anywhere | YesYesYesNoYesNoNoNoSQLBase | YesNoYesNoNoNoNoNoSQLite | YesYesYesYesYesYesYesMaybeSuperbase | YesNoNoNoNoYesNoNoTeradata | YesNoYesNoYesNoNoNoUniData | YesNoYesNoYesNoNoNoUniVerse | YesNoYesNoYesNoNoNoNote (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 usingLinux on zSeries.
Note (2): The database availability depends on Java Virtual Machine not on the operating system
Note (3): Oracle Rdb was originally developed byDEC, 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 onOpenVMS, Solaris, QNX, OS9000 and OS9.
Note (7): Polyhedra also runs onAIX,OSE, Solaris, LynxOS andVxWorks. Previous versions also ran onUltrix, VMS and pSOS. Source code kits allow customers to port to other platforms.
Note (8): MySQL also runs onSolaris, Opensolaris, and can be made from source on other platforms as well.
Note (9): Binaries are not yet available forMac OS X andBSD.
Note (10): CUBRID provides a client interface of its RDBMS forMac OS X called the CUBRID Manager, which can be used for remote CUBRID connection.
[edit]Fundamental features
Information about what fundamental RDBMS features are implemented natively.
MySQL InnoDB is ACID compliant
| ACID | Referential integrity | Transactions | Unicode | Interface | 4th Dimension | YesYesYesYesGUI &SQLADABAS | YesNoYesYesproprietary direct call & SQL (via 3rd party)Adaptive Server Enterprise | YesYesYesYesSQLAdvantage Database Server | YesYesYesYes3API &SQLAltibase | YesYesYes?SQLApache Derby | YesYesYesYesSQLCUBRID | YesYesYesYesGUI &SQLDrizzle | YesYesYesYesSQLDB2 | YesYesYesYesGUI &SQLEmpress Embedded Database | YesYesYesYesAPI &SQLFirebird | YesYesYesYesSQLHSQLDB | YesYesYesYesSQLH2 | YesYesYesYesSQLInformix Dynamic Server | YesYesYesYesSQLIngres | YesYesYesYesSQL & QUELInterBase | YesYesYesYesSQLLinter SQL RDBMS | YesYesYesYesGUI &SQLLucidDB | YesNoNoYesSQLMaxDB | YesYesYesYesSQLMicrosoft Access (JET) | YesYesYesYesGUI &SQLMicrosoft Visual FoxPro | NoYesYesNoGUI &SQLMicrosoft SQL Server | YesYesYesYesGUI &SQLMicrosoft SQL Server Compact (Embedded Database) | YesYesYesYesGUI &SQLMonetDB | YesYesYesYes?MySQL | Yes2Yes2Yes2 except forDDL [27]YesSQLNavibase | YesYesYesYesAPI &GUI & SQLOpenBase SQL | YesYesYesYesGUI &SQLOracle | YesYesYes except for DDL [27]YesAPI &GUI & SQLOracle Rdb | YesYesYesYesSQLOpenLink Virtuoso | YesYesYesYesAPI &GUI & SQLPolyhedra DBMS | YesYesYesYesSQLPostgreSQL | YesYesYesYesGUI &SQLRDM Embedded | YesYesYesYesSQL & APIRDM Server | YesYesYesYesSQL & APIScimoreDB | YesYesYesPartialSQLSQL Anywhere | YesYesYesYesSQLSQLBase | YesYesYesYesAPI &GUI & SQLSQLite | YesYesYesOptional[28]SQLTeradata | YesYesYesYesSQLUniData | YesNoYesYesMultipleUniVerse | YesNoYesYesMultiple | 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 ACID compliance with MySQL, the InnoDB storage engine must be chosen.[29][30]
Note (3): Support for Unicode is new in version 10.0.
[edit]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 | limited ? ?65135200 GB (2 GiBUnicode)200 GB (2 GiBUnicode)64 bits ? ? ?Advantage Database Server | Unlimited16 EiB65530 B65135 / (10+ AvgFieldNameLength)4 GiB ?64 bits ? ?128Apache Derby | UnlimitedUnlimitedUnlimited1012 (5000 in views)2 147 483 647 chars254 (VARCHAR
: 32672) ?0001-01-019999-12-31128CUBRID | 2 EB2 EBUnlimited6400Unlimited1 GB64 bits0001-01-019999-12-31254Drizzle | Unlimited64 TB8 kB10004 GB (longtext, longblob)64 kB (text)64 bits0001999964DB2 | 512 TiB512 TB32 677 B10122 GB32 KiB)64 bits00019999128Empress Embedded Database | Unlimited263-1 bytes2 GB327672 GB2 GB64 bits0000-01-019999-12-3132Firebird | Unlimited1~32 TB65 536 BDepends on data types used.2 GB32 767 B64 bits1003276831HSQLDB | 64 TBUnlimited8Unlimited8Unlimited864 TB7Unlimited8Unlimited80001-01-019999-12-31128H2 | 64 TBUnlimited8Unlimited8Unlimited864 TB7Unlimited864 bits-9999999999999999Unlimited8Informix Dynamic Server | ~128 PB~128 PB32 765 bytes (exclusive of large objects)327654 TB32765103201/01/00011012/31/9999128 bytesIngres | UnlimitedUnlimited256 kB10242 GB32 000 B64 bits0001999932InterBase | Unlimited1~32 TB65 536 BDepends on data types used.2 GB32 767 B64 bits1003276831Linter SQL RDBMS | Unlimited230 rows64 kB (w/o BLOBs), 4 GB (BLOB)2504 GB4 kB64 bits0001-01-012099-12-31128Microsoft Access (JET) | 2 GB2 GB16 MB25564 kB (memo field), 1 GB ("OLE Object" field)255 B (text field)32 bits0100999964Microsoft Visual Foxpro | Unlimited2 GB65 500 B2552 GB16 MB32 bits00019999 ?Microsoft SQL Server | 524 258 TB (32 767 files * 16 TB max file size)524 258 TBUnlimited300002 GB2 GB6126 bits200019999128Microsoft SQL Server Compact (Embedded Database) | 4 GB4 GB8060 bytes1024500 MB4000126 bits 200019999128MySQL 5 | UnlimitedMyISAM storage limits: 256 TB; Innodb storage limits: 64 TB64 kB3409644 GB (longtext, longblob)64 kB (text)64 bits1000999964Oracle | Unlimited (4 GB * block size per tablespace)4 GB * block size (with BIGFILE tablespace)8 kB1000Unlimited4000 B126 bits-4712999930Polyhedra | Limited by available RAM, address space232 rowsUnlimited655364 GB (subject to RAM)4 GB (subject to RAM)32 bits0001-01-018000-12-31255PostgreSQL | Unlimited32 TB1.6 TB250-1600 depending on type1 GB (text, bytea) - stored inline or 2 GB (stored in pg_largeobject)1 GBUnlimited-4713587489763ScimoreDB | Unlimited16 EB8050 B25516 TB8000 B64 bits ? ? ?SQL Anywhere | 104 TB (13 files, each file up to 8 TB (32k pages))Limited by file sizeLimited by file size450002 GB2 GB64 bits0001-01-019999-12-31 ?SQLite | 32 TB (230 pages * 32 kB max page size) ? ?327671 GB1 GB64 bitsNo DATE type9No DATE type9 ?Teradata | UnlimitedUnlimited64 kB wo/lobs (64 GB w/lobs)20482 GB10 00064 bits ?9999-12-31 Select 80991231 (date);30UniVerse | UnlimitedUnlimitedUnlimitedUnlimitedUnlimitedUnlimitedUnlimitedUnlimitedUnlimitedUnlimited | 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 | Note (1): Firebird 2.x maximum database size is effectively unlimited with the largest known database size >980 GB.[31] Firebird 1.5.x maximum database size: 32 TB.
Note (2): limit is 1038usingDECIMAL
datatype[32]
Note (3): InnoDB is limited to 8,000 bytes (excludingVARBINARY
, VARCHAR
, BLOB
, or TEXT
columns)[33]
Note (4): InnoDB is limited to 1,000 columns[33]
Note (6): UsingVARCHAR (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,[34] which work for timestamps between 0000-01-01 00:00:00 and 5352-11-01 10:52:47.
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.
[edit]Tables and views
Information about what tables and views (other than basic ones) are supported natively.
| Temporary table | Materialized view | 4th Dimension | YesPlanned for inclusion in next major releaseADABAS | ??Adaptive Server Enterprise | Yes1NoAdvantage Database Server | YesNo (only common views)Altibase | YesYesApache Derby | YesNoCUBRID | NoNoDrizzle | YesNo4DB2 | YesYesEmpress Embedded Database | YesYesFirebird | YesNo (only common views)HSQLDB | YesNoH2 | YesNoInformix Dynamic Server | YesNo2Ingres | YesPlanned for inclusion in next major releaseInterBase | YesNoLinter SQL RDBMS | YesNoLucidDB | NoNoMaxDB | YesNoMicrosoft Access (JET) | NoNoMicrosoft Visual Foxpro | YesYesMicrosoft SQL Server | YesYes3Microsoft SQL Server Compact (Embedded Database) | YesNoMonetDB | YesNoMySQL | YesNo4OpenBase SQL | YesYesOracle | YesYesOracle Rdb | YesYesOpenLink Virtuoso | YesYesPolyhedra DBMS | NoNo (only common views)PostgreSQL | YesNo5SQL Anywhere | YesYesScimoreDB | NoNoSQLite | YesNoTeradata | YesYesUniData | YesNoUniVerse | YesNoNote (1): Server provides tempdb, which can be used for public and private (for the session) temp tables.[35]
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.[36]
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.[37]
Note (4): Materialized views can be emulated usingstored procedures and triggers.[38]
Note (5): Materialized views can be emulated with stored procedures and triggers usingPL/pgSQL, PL/Perl, PL/Python, or other procedural languages.[39]
[edit]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 | Full-text | Spatial | FOT | 4th Dimension | ?Cluster????????ADABAS | ??????????Adaptive Server Enterprise | NoNoYesNoYesNoNoNoYes?Advantage Database Server | NoNoYesNoYesYesNoNoYes?Apache Derby | NoNoNoNoNoNoNoNoNo[40]?CUBRID | NoNoNoNoYesNoNoNo??Drizzle | NoNoNoNoNoNoNoNoNo?DB2 | No?YesNoYesYesNoNoYes[41]?Empress Embedded Database | YesNoNoYesNoYesNoNoNo?Firebird | NoNoYesNoYes 1NoNoNoNo[42]?HSQLDB | NoNoNoNoNoNoNoNoNo?H2 | NoYesNoNoNoNoNoNoYes[43]?Informix Dynamic Server | YesYesYesYesYesYesYesYesYesYesYesIngres | YesYesIngres v10NoNoIngres v10NoNoNo?InterBase | NoNoNoNoNoNoNoNoNo?Linter SQL RDBMS10 | NoNoNoNoNoNoNoNoYes[44]?LucidDB | NoNoNoNoNoYesNoNoNo?MaxDB | NoNoNoNoNoNoNoNoNo?Microsoft Access (JET) | NoNoNoNoNoNoNoNoNo[45]?Microsoft Visual Foxpro | NoNoYesYesYes2YesNoNoNo?Microsoft SQL Server | ?Non/Cluster & fill factorYes3Yes4No3NoNoNoYes[46]Yes[47]Microsoft SQL Server Compact (Embedded Database) | NoNoNoNoNoNoNoNoNo[48]?MonetDB | NoYesNoNoNoNoNoNo??MySQL | MyISAM tables onlyMEMORY, Cluster (NDB), InnoDB,5 tables onlyNo[49]NoNoNoNoNoMyISAM tables only[50]?Oracle | Yes 11Cluster TablesYesYes 6YesYesNoNoYes[51]Yes[52]Oracle Rdb | NoYes?NoNo?NoNo??OpenLink Virtuoso | YesClusterYesNoNoYesNoNoYes?Polyhedra DBMS | NoYesNoNoNoNoNoNo??PostgreSQL | YesYesYesYesYes7Yes8YesYesYes[53]Postgis[54]ScimoreDB | NoNoNoNoNoNoNoNoYes[55]?SQL Anywhere | NoNoNoNoNoNoNoNoYes?SQLite | YesNoNoNoYesNoNoNoYes[56]SpatiaLite[57]Teradata | NoYesYesYesNoYesNoNo?[58]?UniVerse | YesYesYes3Yes3Yes3NoNoNo?? | R-/R+ tree | Hash | Expression | Partial | Reverse | Bitmap | GiST | GIN | Full-text | Spatial | Note (1): The users need to use a function from freeAdhocUDF library or similar.[59]
Note (2): Can be implemented for most data types using expression-based indexes.
Note (3): Can be emulated by indexing a computed column[60] (doesn't easily update) or by using an "Indexed View"[61] (proper name not just any view works[62])
Note (4): Can be implemented by using an indexed view.[63]
Note (5): InnoDB automatically generates adaptive hash index[64] 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 a future version. Version 8.2 supports a related technique known as "in-memory bitmap scans".
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
[edit]Database capabilities
| Union | Intersect | Except | Inner joins | Outer joins | Inner selects | Merge joins | Blobs andClobs | Common Table Expressions | Windowing Functions | Parallel Query | 4th Dimension | YesYesYesYesYesNoNoYes???ADABAS | Yes??????????Adaptive Server Enterprise | Yes??YesYesYesYesYes??YesAdvantage Database Server | YesNoNoYesYesYesYesYes?No?Altibase | Yes??YesYesYesYesYes???Apache Derby | YesYesYesYesYes??YesNoNo?CUBRID | YesYesYesYesYesYesYesYesNoNo?Drizzle | YesNoNoYesYesYesNoYesNoNoNo[65]DB2 | YesYesYesYesYesYesYesYesYesYesYes[66]Empress Embedded Database | YesYesYesYesYesYesYesYes???Firebird | Yes??YesYesYesYesYesYesYes?HSQLDB | YesYesYesYesYesYesNoYesYesNoNoH2 | YesYesYesYesYesYesNoYesexperimental[67]No[68]?Informix Dynamic Server | Yes?Yes, via MINUSYesYesYesYesYesYes?Yes[69]Ingres | YesNoNoYesYesYesYesYesNoNo?InterBase | Yes??YesYes??Yes???Linter SQL RDBMS | YesYesYesYesYesYesYesYesNoYes?LucidDB | YesYesYesYesYesYesYesNo???MaxDB | Yes??YesYesYesNoYes???Microsoft Access (JET) | YesNoNoYesYesYesNoYesNoNo?Microsoft Visual Foxpro | Yes??YesYesYes?Yes???Microsoft SQL Server | YesYes (2005 and beyond)Yes (2005 and beyond)YesYesYesYesYesYesYesYes[70]Microsoft SQL Server Compact (Embedded Database) | YesNoNoYesYes?NoYesNoNo?MonetDB | ???????????MySQL | YesNoNoYesYesYesNoYesNo[71]NoNo[72]OpenBase SQL | NoNoNoYesYesYesYesYes???Oracle | YesYesYes, via MINUSYesYesYesYesYesYes 1YesYes[73]Oracle Rdb | Yes??YesYesYesYesYes???OpenLink Virtuoso | Yes??YesYesYes?Yes???Polyhedra DBMS | YesYesYesYesNo??Yes???PostgreSQL | YesYesYesYesYesYesYesYesYesYesNo[74]ScimoreDB | Yes??YesLEFT onlyYesYesYes???SmallSQL | ???????????SQL Anywhere | YesYesYesYesYesYesYesYesYesYes?SQLite | YesYesYesYesLEFT onlyYes?YesNoNo?Teradata | YesYesYesYesYesYesYesYesNoYesYesUniVerse | YesYesYesYesYesYesYesNoNoNo? | Union | Intersect | Except | Inner joins | Outer joins | Inner selects | Merge joins | Blobs andClobs | Common Table Expressions | Windowing Functions | Parallel Query | Note (1): Recursive CTEs introduced in 11gR2 supersedes similar construct called CONNECT BY
[edit]Data types
| Type system | Integer | Floating point | Decimal | String | Binary | Date/Time | Boolean | Other | CUBRID[75] | StaticSMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit)FLOAT, REAL(32-bit), DOUBLE(64-bit)DECIMAL, NUMERICCHAR, VARCHAR, NCHAR, NVARCHAR, CLOBBLOBDATE, DATETIME, TIME, TIMESTAMPBITMONETARY, BIT VARYING, SET, MULTISET, SEQUENCEDrizzle[76] | StaticINT (32-bit), BIGINT (64-bit)DOUBLE (aka REAL) (64-bit)DECIMALBINARY, VARCHAR, VARBINARY, TEXT,BLOBDATETIME, DATE, TIMESTAMP ENUM, SERIALEmpress Embedded Database | StaticTINYINT, SQL_TINYINT or INTEGER8SMALLINT, SQL_SMALLINT or INTEGER16 INTEGER, INT, SQL_INTEGER or INTEGER32 BIGINT, SQL_BIGINT or INTEGER64
REAL, SQL_REAL or FLOAT32DOUBLE PRECISION, SQL_DOUBLE or FLOAT64 FLOAT or SQL_FLOAT EFLOAT
DECIMAL, DEC, NUMERIC, SQL_DECIMAL or SQL_NUMERICDOLLAR
CHARACTER,ECHARACTER, CHARACTER VARYING, NATIONAL CHARACTER, NATIONAL CHARACTER VARYING and NLSCHARACTER
CHARACTER LARGE OBJECT, TEXT, NATIONAL CHARACTER LARGE OBJECT, and NLSTEXT
BINARY LARGE OBJECT or BLOBBULK
DATE,EDATE, TIME, ETIME, EPOCH_TIME, TIMESTAMP, MICROTIMESTAMP
BOOLEANSEQUENCE 32SEQUENCE
HSQLDB[77] | StaticTINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit)DOUBLE (64-bit)DECIMAL, NUMERICCHAR, VARCHAR, LONGVARCHAR, CLOBBINARY, VARBINARY, LONGVARBINARY, BLOBDATE, TIME, TIMESTAMP, INTERVALBOOLEANOTHER (object), BIT, BIT VARYING, ARRAYInformix Dynamic Server[78] | StaticSMALLINT (16-bit), INT (32-bit), INT8 (64-bit proprietary), BIGINT (64-bit)SMALLFLOAT (32-bit), FLOAT (64-bit)DECIMAL (32 digits float/fixed), MONEYCHAR, VARCHAR, NCHAR, NVARCHAR, LVARCHAR, CLOB, TEXTTEXT, BYTE, BLOB, CLOBDATE, DATETIME, INTERVALBOOLEANSET, LIST, MULTISET, ROW, TIMESERIES, SPATIAL, USER DEFINED TYPESIngres[79] | StaticTINYINT (8-bit), SMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit)FLOAT4 (32-bit), FLOAT (64-bit)DECIMALC, CHAR, VARCHAR, LONG VARCHAR, NCHAR, NVARCHAR, LONG NVARCHAR, TEXTBYTE, VARBYTE, LONG VARBYTE (BLOB)DATE, ANSIDATE, INGRESDATE, TIME, TIMESTAMP, INTERVALN/AMONEY, OBJECT_KEY, TABLE_KEY, USER-DEFINED DATA TYPES (via OME)Microsoft SQL Server[80] | StaticTINYINT, SMALLINT, INT, BIGINTFLOAT, REALNUMERIC, DECIMAL, SMALLMONEY, MONEYCHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXTBINARY, VARBINARY, IMAGE, FILESTREAMDATE, DATETIMEOFFSET, DATETIME2, SMALLDATETIME, DATETIME, TIMEBITCURSOR, TIMESTAMP, HIERARCHYID, UNIQUEIDENTIFIER, SQL_VARIANT, XML, TABLEMicrosoft SQL Server Compact (Embedded Database)[81] | StaticTINYINT, SMALLINT, INT, BIGINTFLOAT, REALNUMERIC, DECIMAL, MONEYNCHAR, NVARCHAR, NTEXTBINARY, VARBINARY, IMAGEDATETIMEBITTIMESTAMP, ROWVERSION, UNIQUEIDENTIFIER, IDENTITY, ROWGUIDCOLMySQL[82] | StaticTINYINT (8-bit), SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit)FLOAT (32-bit), DOUBLE (aka REAL) (64-bit)DECIMALCHAR, BINARY, VARCHAR, VARBINARY, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXTTINYBLOB, BLOB, MEDIUMBLOB, LONGBLOBDATETIME, DATE, TIMESTAMP, YEARBOOLEAN (aka BOOL) = synonym for TINYINTENUM, SET, GIS data types (Geometry, Point, Curve, LineString, Surface, Polygon, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon)Oracle[83] | Static + Dynamic (through ANYDATA)NUMBERBINARY_FLOAT, BINARY_DOUBLENUMBERCHAR, VARCHAR2, CLOB, NCLOB, NVARCHAR2, NCHARBLOB, RAW, LONGRAW, BFILEDATE, TIMESTAMP (with/without TIMEZONE), INTERVALN/ASPATIAL, IMAGE, AUDIO, VIDEO, DICOM, XMLTypePolyhedra | StaticINTEGER8 (8-bit), INTEGER(16-bit), INTEGER (32-bit)FLOAT32 (32-bit), FLOAT (aka REAL; 64-bit)N/AVARCHAR, LARGE VARCHAR (aka CHARACTER LARGE OBJECT)LARGE BINARY (aka BINARY LARGE OBJECT)DATETIMEBOOLEANN/APostgreSQL[84] | StaticSMALLINT (16-bit), INTEGER (32-bit), BIGINT (64-bit)REAL (32-bit), DOUBLE PRECISION (64-bit)DECIMAL, NUMERICCHAR, VARCHAR, TEXTBYTEADATE, TIME (with/without TIMEZONE), TIMESTAMP (with/without TIMEZONE), INTERVALBOOLEANENUM, POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE, CIDR, INET, MACADDR, BIT, UUID, XML, arraysSQLite[85] | DynamicINTEGER (64-bit)REAL (aka FLOAT, DOUBLE) (64-bit)N/ATEXT (aka CHAR, CLOB)BLOBN/AN/AN/AUniData | DynamicN/AN/AN/AN/AN/AN/AN/AN/AUniVerse | DynamicN/AN/AN/AN/AN/AN/AN/AN/A | Type system | Integer | Floating point | Decimal | String | Binary | Date/Time | Boolean | Other | [edit]Other objects
Information about what other objects are supported natively.
| Data Domain | Cursor | Trigger | Function 1 | Procedure 1 | External routine 1 | 4th Dimension | YesNoYesYesYesYesADABAS | ?Yes?Yes?Yes??Adaptive Server Enterprise | YesYesYesYesYesYesAdvantage Database Server | YesYesYesYesYesYesApache Derby | NoYesYesYes 2Yes 2Yes 2CUBRID | YesYesYesYesYes 2YesDrizzle | YesYesYes 4Yes 4Yes 4Yes 4Empress Embedded Database | Yes via RANGE CHECKYesYesYesYesYesDB2 | Yes via CHECK CONSTRAINTYesYesYesYesYesFirebird | YesYesYesYesYesYesHSQLDB | YesNoYesYesYesYesH2 | YesNoYes 2Yes 2Yes 2YesInformix Dynamic Server | Yes via CHECKYesYesYesYesYesIngres | YesYesYesYesYesYesInterBase | YesYesYesYesYesYesLinter SQL RDBMS | NoYesYesYesYesNoLucidDB | NoYesNoYes 2Yes 2Yes 2MaxDB | YesYesYesYesYes?Microsoft Access (JET) | YesNoNoNoYes, But single DML/DDL OperationYesMicrosoft Visual Foxpro | NoYesYesYesYesYesMicrosoft SQL Server | Yes (2000 and beyond)YesYesYesYesYesMicrosoft SQL Server Compact (Embedded Database) | NoYesNoNoNoNoMonetDB | NoNoYesYesYesYesMySQL | No 3YesYesYesYesYesOpenBase SQL | YesYesYesYesYesYesOracle | YesYesYesYesYesYesOracle Rdb | YesYesYesYesYesYesOpenLink Virtuoso | YesYesYesYesYesYesPolyhedra DBMS | NoNoYesYesYesYesPostgreSQL | YesYesYesYesYesYesScimoreDB | NoNoNoNoYesYesSQL Anywhere | YesYesYesYesYesYesSQLite | NoNoYesNoNoYesTeradata | NoYesYesYesYesYesUniData | NoNoYesYesYesYesUniVerse | NoNoYesYesYesYes | Data Domain | Cursor | Trigger | Function 1 | Procedure 1 | External routine 1 | Note (1): Bothfunction and procedure refer to internal routines written in SQL and/or procedural language likePL/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, LucidDB, and CUBRID, users codefunctions and procedures in Java.
Note (3): ENUM datatype exist. CHECK clause is parsed, but not enforced in runtime.
Note (4): In Drizzle the user codesfunctions and procedures in C++.
[edit]Partitioning
Information about what partitioning methods are supported natively.
| Range | Hash | Composite (Range+Hash) | List | Native Replication API | 4th Dimension | ?????ADABAS | ?????Adaptive Server Enterprise | YesYesNoYes?Advantage Database Server | NoNoNoNoYesApache Derby | NoNoNoNo?CUBRID | YesYesNoYes?IBM DB2 | YesYesYesYes?Empress Embedded Database | NoNoNoNoYesFirebird | NoNoNoNoNoHSQLDB | NoNoNoNoNoH2 | NoNoNoNoNoInformix Dynamic Server | YesYesYesYesYesIngres | YesYesYesYesNoInterBase | NoNoNoNoYesLinter SQL RDBMS | NoNoNoNoNoMaxDB | NoNoNoNo?Microsoft Access (JET) | NoNoNoNoYesMicrosoft Visual Foxpro | NoNoNoNoNoMicrosoft SQL Server | YesNoNoNo?Microsoft SQL Server Compact (Embedded Database) | NoNoNoNoYesMonetDB | Yes (M5)Yes (M5)Yes (M5)No?MySQL | YesYesYesYes?OpenBase SQL | ?????Oracle | YesYesYesYes?Oracle Rdb | YesYes???OpenLink Virtuoso | YesNoNoNo?Polyhedra DBMS | NoNoNoNoNoPostgreSQL | Yes1Yes1Yes1Yes1?ScimoreDB | NoYesNoNoYesSQL Anywhere | NoNoNoNo?SQLite | NoNoNoNo?Teradata | YesYesYesYes?UniVerse | YesYesYesYesYes | Range | Hash | Composite (Range+Hash) | List | Native Replication API | 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.[86]
[edit]Access control
Information about access control functionalities (work in progress).
| Native network encryption1 | Brute-force protection | Enterprise directory compatibility | Password complexity rules2 | Patch access3 | Run unprivileged4 | Audit | Resource limit | Separation of duties (RBAC)5 | Security Certification | Adaptive Server Enterprise | Yes (optional; to pay)YesYes (optional ?)YesPartial (need to register; depend on which product)[87]YesYesYesYesYes (EAL4+ 1)Advantage Database Server | YesNoNoNo?YesNoNoYes?DB2 | Yes?Yes (LDAP, Kerberos…)Yes?YesYesYesYesYes (EAL4+6)Empress Embedded Database | ??NoNoYesYesYesNoYesNoFirebird | NoYes[88]Yes (Windows trusted authenification)NoPartial (no security page)[89]YesNoNoNo7?HSQLDB | YesNoNoNoYesYesNoNoYesNoH2 | YesYes?No?Yes?YesYesNoInformix Dynamic Server | Yes?Yes10?10YesYesYesYesYes?Linter SQL RDBMS | Yes (with SSL)YesNoYes (length only)NoYesYesYesYesYesMySQL | Yes (SSL with 4.0)NoYes (with 5.5)NoPartial (no security page)[90]Yes???8NoOpenBase SQL | Yes?Yes (Open Directory, LDAP)No??????Microsoft SQL Server | Yes?Yes (Microsoft Active Directory)YesYesYesYes (From 2008)YesYesYes (EAL1+1)Microsoft SQL Server Compact (Embedded Database) | No (not relevant, only file permissions)No (not relevant)No (not relevant)No (not relevant)YesYes (file access)YesYesNo?Oracle | YesYesYesYes?YesYesYesYesYes (EAL4+1)PostgreSQL | YesNoYes (LDAP, Kerberos…9)Yes (as of 9.0 with passwordcheck module)Yes[91]YesNoYesNoYes (EAL11)SQL Anywhere | Yes?Yes (Kerberos)Yes?YesYesNoYesYes (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)[92]Yes (file access)YesYesNoNo | Native network encryption1 | Brute-force protection | Enterprise directory compatibility | Password complexity rules2 | Patch access3 | Run unprivileged4 | Audit | Resource limit | Separation of duties (RBAC)5 | Security Certification | 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[93]
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[94]
Note (9): Authentication methods[95]
Note (10): Informix Dynamic Server supports PAM and other configurable authentication. By default uses OS authentication.
[edit]Databases vs schemas (terminology)
This section may contain original research. Please improve it by verifying the claims made and adding references. Statements consisting only of original research may be removed. More details may be available on thetalk page. (June 2010)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 memberoperator 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,PostgreSQL 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 thatCREATE SCHEMA
and CREATE DATABASE
are analogs. It can therefore be said that MySQL 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 does not even attempt to support true 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 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 one database is all that is needed for one project, per the definition of database. MySQL proponents maintain that schemas have no legitimate purpose when the functionality can be achieved with databases. Postgres adheres to the SQL specification, in a more intuitive fashion (bottom-up), while MySQL’spragmatic counterargument allows their users to get the job done while creating conceptual confusion.
[edit]See also