Comments 0

Document transcript

www.aurateknology-inc.org

Page1

Comparison of relational database management systems

The following tables compare general and technical information for a number ofrelational databasemanagement systems. Please see the individual products' articles for further information. This articleisnot all-inclusive or necessarily up-to-date. Unless otherwise specified in footnotes, comparisons arebased on the stable versions without any add-ons, extensions or external programs.

Operating system support

The operating systems the RDBMSes can run on.

Database

Windows

Mac OS X

Linux

BSD

UNIX

AmigaOS

Symbian

z/OS

4th Dimension

Yes

Yes

No

No

No

No

No

No

DB2

Yes

Yes(ExpressC)

Yes

No

Yes

No

No

Yes

Empress EmbeddedDatabase

Yes

Yes

Yes

Yes

Yes

No

No

No

Firebird

Yes

Yes

Yes

Yes

Yes

No

No

Maybe

HSQLDB

Yes

Yes

Yes

Yes

Yes

No

No

Yes

H2

Yes

Yes

Yes

Yes

Yes

No

No

Yes

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

Yes

Yes

Yes

Yes

Yes

No

No

No

LucidDB

Yes

Yes

Yes

No

No

No

No

No

MariaDB

Yes

Yes

Yes

Yes

Yes

No

No

No

www.aurateknology-inc.org

Page2

Database

Windows

Mac OS X

Linux

BSD

UNIX

AmigaOS

Symbian

z/OS

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

Microsoft SQL ServerCompact (EmbeddedDatabase)

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

Yes

Omnis Studio

Yes

Yes

Yes

No

No

No

No

No

OpenBase SQL

Yes

Yes

Yes

Yes

Yes

No

No

No

Oracle

Yes

Yes

Yes

No

Yes

No

No

Yes

Oracle Rdb

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 (OEMonly)

Yes

No

No

No

No

No

Polyhedra

Yes

No

Yes

No

Yes

No

No

No

PostgreSQL

Yes

Yes

Yes

Yes

Yes

No

No

No

Postgres Plus StandardServer

Yes

Yes

Yes

Yes

Yes

No

No

No

Postgres Plus AdvancedServer

Yes

Yes

Yes

Yes

Yes

No

No

No

www.aurateknology-inc.org

Page3

Database

Windows

Mac OS X

Linux

BSD

UNIX

AmigaOS

Symbian

z/OS

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

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

UniVerse

Yes

No

Yes

No

Yes

No

No

No

www.aurateknology-inc.org

Page4

Fundamental features

Information about what fundamental RDBMS features are implemented natively.

Database

ACID

Referentialintegrity

Transactions

Unicode

Interface

4th Dimension

Yes

Yes

Yes

Yes

GUI

&SQL

ADABAS

Yes

No

Yes

Yes

Template:Proprietarydirect call & sql(via3rdparty)

Adaptive Server Enterprise

Yes

Yes

Yes

Yes

SQL

Advantage Database Server

Yes

Yes

Yes

Yes

3

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

Empress EmbeddedDatabase

Yes

Yes

Yes

Yes

API

&SQL

Firebird

Yes

Yes

Yes

Yes

SQL

HSQLDB

Yes

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

Yes

SQL

www.aurateknology-inc.org

Page5

Database

ACID

Referentialintegrity

Transactions

Unicode

Interface

MariaDB

?

?

?

?

?

MaxDB

Yes

Yes

Yes

Yes

SQL

Microsoft Access

Yes

Yes

Yes

Yes

GUI

&SQL

Microsoft Visual Foxpro

No

Yes

Yes

No

GUI

&SQL

Microsoft SQL Server

Yes

Yes

Yes

Yes

GUI

&SQL

Microsoft SQL Server Compact(Embedded Database)

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

GUI

&SQL

Postgres Plus Standard Server

Yes

Yes

Yes

Yes

SQL

Postgres Plus Advanced Server

Yes

Yes

Yes

Yes

SQL

RDMEmbedded

Yes

Yes

Yes

Yes

SQL

&API

RDM Server

Yes

Yes

Yes

Yes

SQL

&API

ScimoreDB

Yes

Yes

Yes

Partial

SQL

www.aurateknology-inc.org

Page6

Database

ACID

Referentialintegrity

Transactions

Unicode

Interface

SQL Anywhere

Yes

Yes

Yes

Yes

SQL

SQLBase

Yes

Yes

Yes

Yes

API

&GUI

&SQL

SQLite

Yes

Yes

Yes

Optional

SQL

Teradata

Yes

Yes

Yes

Yes

SQL

UniVerse

Yes

Yes

Yes

Yes

Multiple

www.aurateknology-inc.org

Page7

Limits

Information about data size limits.

Database

Max DBsize

Max tablesize

Maxrowsize

Maxcolumns perrow

MaxBlob/Clob size

MaxCHARsize

MaxNUMBER size

MinDATEvalue

MaxDATEvalue

Maxcolumnnamesize

4thDimension

limited

?

?

65135

200 GB(2GiB

Unicode)

200 GB(2GiB

Unicode)

64 bits

?

?

?

AdvantageDatabaseServer

Unlimited

16 EB (16EiB)

65530B

65135/(10+AverageFieldNameLength)

4 GB (4GiB)

?

64 bits

?

?

128

ApacheDerby

Unlimited

Unlimited

Unlimited

1012 (5000in views)

2,147,483,647chars

254(VARCHAR:32672)

?

0001-01-01

9999-12-31

128

CUBRID

2 EB

2 EB

?

6400

1 GB(GLOtypesupported)

1

GB

64 bits

0001

9999

254

DB2

512 TB(512TiB)

512 TB

32,677B

1012

2 GB

32 KB(32KiB)

64 bits

0001

9999

128

EmpressEmbeddedDatabase

Unlimited

263-1 bytes

2 GB

32,767

2 GB

2 GB

64 bits

0000-01-01

9999-12-31

32

www.aurateknology-inc.org

Page8

Database

Max DBsize

Max tablesize

Maxrowsize

Maxcolumns perrow

MaxBlob/Clob size

MaxCHARsize

MaxNUMBER size

MinDATEvalue

MaxDATEvalue

Maxcolumnnamesize

Firebird

Unlimited

~32 TB

65,536B

Depends ondata typesused.

2 GB

32,767B

64 bits

100

32768

31

HSQLDB

64 TB

Unlimited

Unlimited

Unlimited

64 TB

Unlimited

Unlimited

0001-01-01

9999-12-31

128

H2

64 TB

Unlimited

Unlimited

Unlimited

64 TB

Unlimited

64 bits

-99999999

99999999

Unlimited

InformixDynamicServer

~128PB

~128PB

32765bytes(exclusive oflargeobjects)

32765

4TB

32765

10^32

12/31/1900

12/31/9999

128bytes

Ingres

Unlimited

Unlimited

256 KB

1024

2 GB

32,000B

64 bits

0001

9999

32

InterBase

Unlimited

~32 TB

65,536B

Depends ondata typesused.

2 GB

32,767B

64 bits

100

32768

31

Linter SQLRDBMS

Unlimited

2^30 rows

64KB(w/oBLOBs), 4GB(BLOB)

250

4GB

4KB

64 bits

0001-01-01

2099-12-31

128

www.aurateknology-inc.org

Page9

Database

Max DBsize

Max tablesize

Maxrowsize

Maxcolumns perrow

MaxBlob/Clob size

MaxCHARsize

MaxNUMBER size

MinDATEvalue

MaxDATEvalue

Maxcolumnnamesize

MicrosoftAccess

2 GB

2 GB

16 MB

255

64 KB(memofield), 1GB ("OLEObject"field)

255 B(textfield)

32 bits

0100

9999

?

MicrosoftVisualFoxpro

Unlimited

2 GB

65,500B

255

2 GB

16 MB

32 bits

0001

9999

?

MicrosoftSQLServer

524,258TB(32,767files * 16TB maxfile size)

524,258TB

Unlimited

30000

2 GB

2 GB

126bits

0001

9999

128

MicrosoftSQLServerCompact(EmbeddedDatabase)

4 GB

4 GB

8060Bytes

1024

500 MB

4000

126bits

0001

9999

128

MySQL

5

Unlimited

MyISAMstoragelimits:256TB;Innodbstoragelimits:64TB

64 KB

4096

4 GB(longtext,longblob)

64 KB(text)

64 bits

1000

9999

64

www.aurateknology-inc.org

Page10

Database

Max DBsize

Max tablesize

Maxrowsize

Maxcolumns perrow

MaxBlob/Clob size

MaxCHARsize

MaxNUMBER size

MinDATEvalue

MaxDATEvalue

Maxcolumnnamesize

Oracle

Unlimited(4 GB *block sizepertablespace)

4 GB *block size(withBIGFILEtablespace)

8KB

1000

Unlimited

4000 B

126bits

-4712

9999

30

Polyhedra

Limitedonly byavailableRAM,addressspace

232

rows

Unlimited

65536

4 GB(subjectto RAM)

4 GB(subjecttoRAM)

32 bits

0001-01-01

8000-12-31

255

PostgreSQL

Unlimited

32 TB

1.6 TB

250-1600dependingon type

1 GB(text,bytea)-

storedinline or2 GB(storedinpg_largeobject)

1 GB

Unlimited

-4713

5874897

63

PostgresPlusStandardServer

Unlimited

32 TB

1.6 TB

250-1600dependingon type

1 GB(text,bytea)-

storedinline or2 GB(storedinpg_large1 GB

Unlimited

-4713

5874897

63

www.aurateknology-inc.org

Page11

object)

Database

Max DBsize

Max tablesize

Maxrowsize

Maxcolumns perrow

MaxBlob/Clob size

MaxCHARsize

MaxNUMBER size

MinDATEvalue

MaxDATEvalue

Maxcolumnnamesize

PostgresPlusAdvancedServer

Unlimited

32 TB

1.6 TB

250-1600dependingon type

1 GB(text,bytea)-

storedinline or2 GB(storedinpg_largeobject)

1 GB

Unlimited

-4713

5874897

63

ScimoreDB

Unlimited

16 EB

8050 B

255

16 TB

8000 B

64 bits

?

?

?

SQLAnywhere

104 TB(13 files,each fileup to 8 TB(32kpages))

Limited byfile size

Limited byfilesize

45000

2 GB

2 GB

64 bits

0001-01-01

9999-12-31

?

SQLite

32 TB (230

pages *32 KBmax pagesize)

?

?

32767

1 GB

1 GB

64 bits

NoDATEtype9

NoDATEtype9

?

www.aurateknology-inc.org

Page12

Database

Max DBsize

Max tablesize

Maxrowsize

Maxcolumns perrow

MaxBlob/Clob size

MaxCHARsize

MaxNUMBER size

MinDATEvalue

MaxDATEvalue

Maxcolumnnamesize

Teradata

Unlimited

Unlimited

64 KBwo/lobs (64GBw/lobs)

2048

2 GB

10,000

64 bits

?

9999-12-31Select80991231(date);

30

UniVerse

Unlimited

Unlimited

Unlimited

Unlimited

Unlimited

Unlimited

Unlimited

Unlimited

Unlimited

Unlimited

www.aurateknology-inc.org

Page13

Tables and views

Information about what tables and views

(other than basic ones) are supported natively.

Database

Temporarytable

Materialized view

4th Dimension

Yes

Planned for inclusion in nextmajor release

ADABAS

?

?

Adaptive Server Enterprise

Yes

No

Advantage Database Server

Yes

No (only common views)

Altibase

Yes

Yes

Apache Derby

Yes

No

CUBRID

No

No

DB2

Yes

Yes

Empress Embedded Database

Yes

Yes

Firebird

Yes

No (only common views)

HSQLDB

Yes

No

H2

Yes

No

Informix Dynamic Server

Yes

No

Ingres

Yes

Planned for inclusion in nextmajor release

InterBase

Yes

No

Linter SQL RDBMS

Yes

No

LucidDB

No

No

www.aurateknology-inc.org

Page14

Database

Temporarytable

Materialized view

MaxDB

Yes

No

Microsoft Access

Yes

No

Microsoft Visual Foxpro

Yes

Yes

Microsoft SQL Server

Yes

Yes

Microsoft SQL Server Compact (Embedded Database)

Yes

No

MonetDB

Yes

No

MySQL

Yes

No

OpenBase SQL

Yes

Yes

Oracle

Yes

Yes

Oracle Rdb

Yes

Yes

OpenLink Virtuoso

Yes

Yes

Polyhedra DBMS

No

No (only common views)

PostgreSQL

Yes

Planned forinclusion in 9.1

Postgres Plus Standard Server

Yes

No

Postgres Plus Advanced Server

Yes

No

SQL Anywhere

Yes

Yes

ScimoreDB

No

No

SQLite

Yes

No

Teradata

Yes

Yes

UniVerse

Yes

No

www.aurateknology-inc.org

Page15

Indexes

Information about what indexes (other than basic B-/B+ treeindexes) are supported natively.

specification makes clear what an "SQL schema" is; however, different databasesimplement it incorrectly. To compound this confusion the functionality can, when incorrectlyimplemented, overlap with that of the parent-database. An SQL schema is simply anamespace

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 truefully (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 anotherlike named table "foo". The following is pseudo code:



select * from db1.foo

vs.select * from db2.foo

(no explicit schema between dband table)



select * from [db1.]default.foo

vs.select * from [db1.]alternate.foo

(noexplicit db prefix)

The problem that arises is that formerMySQL

users will create multiple databases for oneproject. 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

withdatabase, such thatcreate schema, andcreatedatabase

are analogs. It can be said that MySQL therefore, has implemented cross-tablefunctionality, skipped schema functionality entirelyand provided similar functionality into theirimplementation of a database. In summary, Postgres fully supports schemas but lacks somefunctionality MySQL has with databases, while MySQL doesn't even attempt to support trueschemas.

Oracle has its own spin

where creating a user is synonymous with creating a schema. Thus aDBA can create a user called PROJECT and then create a table PROJECT.TABLE. Users canexist without schema objects, but an object is always associated with an owner (though thatowner maynot 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 isindependent of replication, which can also be used, whereby the data is copiedfor use bydifferent server. In the Oracle view, the 'database' is a set of files which contains the data whilethe '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 communitiesmaintain that generally one database is all that is needed for one project. MySQL proponentsmaintain that schemas have no legitimate purpose when the functionality can be achieved withdatabases. Postgres adheres tomore of the SQL specification, in a more intuitive fashionwww.aurateknology-inc.org

Page36

(bottom-up), while MySQL'spragmatic

counterargument allows their users to get the job donewithout any major drawback.