Talk:Comparison of relational database management systems/Archive 2006

Bitmap indexes

The DB2 support for bitmapped indexes is questionable, see [this usenet discussion]. TroelsArvin 13:06, 9 Mar 2005 (UTC)

It seems like the same discussion applies to PostGreSQL - from what I understood so far, DB2 and PostGreSQL both create the bitmap-indices during runtime in memory only instead of storing the index persistantly, as it is done by Oracle. I have to admit though, that I have no deeper understanding how this works. Can anybody contribute to this? Maybe the distinction between temporary/ersitant can be added as footnote? -- Semiliki 12:10, 7 July 2006 (UTC)[reply]

Stored procedures vs. user defined functions

The page currently has a "User defined functions" column and a "Stored procedure" column. If we want to distinguish between user defined functions and user defined procedures, then the "Stored procedure" column should be renamed. But before I do that: Does anyone know why both columns are there? TroelsArvin 18:41, 9 Mar 2005 (UTC)

A stored procedures is usually PL/SQL (or similar languages); while a user defined function is not (e.g. linking to host language like PHP, or object files like .obj. --Minghong 10:24, 10 Mar 2005 (UTC)

In SQL, there are two types of "SQL-invoked routines": Functions and procedures. They are almost identical, except for details of how they are invoked and how data are returned. DBMS suport is a bit different for the two routine types, by the way (PostgreSQL supports functions, not procedures, for example). I think you are talking about "external routines" which are routines (functions or procedures) executed by code written in a non-SQL language. What I find interesting to list is:

trigger support
support for SQL-routines (whatever kind)
what external languages are supported
support for user defined types

TroelsArvin 10:58, 10 Mar 2005 (UTC)

Not sure about standard SQL. But I'm talking about UDF in Firebird [1] and SQLite [2]. Maybe CREATE FUNCTION is not creating a UDF? I thought it was the same. --Minghong 11:04, 10 Mar 2005 (UTC)

The terminology I've used matches standard SQL. And the same terminology is used in the Oracle, PostgreSQL and DB2 documentation. TroelsArvin 11:21, 10 Mar 2005 (UTC)

I've tried unifying the "Stored procedures" and "User defined functions" columns. TroelsArvin 12:40, 10 Mar 2005 (UTC)

Minghong, you have undone my unification, and then introduced two errors, and general confusion:

Error 1: The page currently says: "User defined function refers to external routines written in the host languages, such as C, Java, Cobol, etc.". This is wrong: A user defined function can be written in either SQL or an external language. If we want to display the difference between functions written in SQL and external binaries/scripts, then the term "external" needs to be emphasized, as "user defined function" is too vague.
Error 2: The page now states that PostgreSQL supports procedures - which is wrong (PostgreSQL has no 'CALL ...' construct). My previous edit removed that error.
Worse: The page now has columns for both functions and procedures (in addition to "User defined function"). Why have both, when the difference really is minimal (see my previous comment). And, if both are retained: At least, you need to specify the difference, if you introduce the separation of concepts.

TroelsArvin 14:26, 10 Mar 2005 (UTC)

Sorry for undoing your change. But unification makes it ambiguous. Procedure and Function are two things as there are 2 statements: CREATE PROCEDURE and CREATE FUNCTION (there are more, like packages, rountine, etc, but these are not common). If the differences are really that minimal, maybe combine the 2 columns. For the team UDF, checkout the SQLite, Firebird, MySQL, and PostgreSQL manuals. They consistently use the term "UDF" for external functions (well, DB2 seems not to follow this convention). OK, my fault, PostgreSQL does not support procedures (that's why 2 columns is better than 1 column: we can show that difference). --Minghong 15:24, 10 Mar 2005 (UTC)

I'm not sure it's very informative to say that PostgreSQL does not support stored procedures. Sure, it does not implement CREATE PROCEDURE, but that is mere syntax — functions in PostgreSQL can do much (although not all) of what can be done using stored procedures in other database systems. Not supporting "CALL", for example, is trivial -- it would merely be syntax sugar for SELECT some_func(). I think we need to decide on a clear definition of what the distinction between a "stored procedure" and a "function" is. Neilc 03:20, 19 Mar 2005 (UTC)

Meanwhile, MySQL supports all three types of routines discussed here. The table now posits that "functions" are unsupported. --Juha001 11:11, 7 April 2006 (UTC)[reply]

MySQL update suggestions

In Fundamental Features, MySQL support for ACID, Referential Integrity and Transactions is "Depends' with a note that the default table type doesn't support them. This is now less true: On Windows the installer will ask if you want transactions and if you do, the default database is set to InnoDB. *nix still ships defaulting to MyISAM. It might be more appropriate to make this a yes with note than a depends with note. Either way, the note needs updating. Anyone who wants transactions will be using InnoDB, so it seems unhelpful to say maybe.

In Indexes, Hash is described as for Heap only. Heap is now called Memory and InnoDB, while it can't have an explicit heap index, automatically creates internal hashed indexes based on its analysis of the need for them. The Memory(formerly heap) engine supports btree indexes now. [3] Partial as no seems simply inaccurate, since you can use partial indexes.[4]

In partitioning, you can place InnoDB tables into their own tablespace from 4.1 on. Seems a "table-based" column may be useful in this section, since the PG v MySQL v. Commercial external link article simply says "yes" to MySQL supporting partitioning. Jamesday 8 July 2005 06:22 (UTC)

About the ACID aspect: I believe that "depends" is still the best and most correct way to characterize MySQL's transaction support. As a developer, it's important that when you code your app for MySQL, you can't count on having transactional behaviour. TroelsArvin 8 July 2005 06:48 (UTC)

As a developer, if you don't know what tools or languages you are working with, you can't count on having any specific behaviour at all, can you? --Juha001 11:00, 7 April 2006 (UTC)[reply]

It's included in all of the 4.0 and later builds MySQL distributes, so it's a completely standard feature. Jamesday 13:28, 17 July 2005 (UTC)[reply]

Have MySQL 4.x's (and 5.x's) MyISAM tables suddenly become transactional? TroelsArvin 14:56, 17 July 2005 (UTC)[reply]

Agree, that doesn't sound right. --minghong 15:54, 19 July 2005 (UTC)[reply]

About partial indexes, at least according to that link MySQL implements a rather different and more limited concept of "partial indexes" than does Postgres. In Postgres, a partial index is defined on an arbitrary predicate (e.g. CREATE INDEX foo_bar_idx ON foo (bar) WHERE (bar > 3)); that index can be used for a query if the index's predicate includes the query's predicate (e.g. SELECT * FROM foo WHERE bar = 5; would use the index, whereas WHERE bar = 2 would not). I'm not sure if there is a standard definition of "partial indexes", but certainly just adding "Yes" for MySQL would be misleading.

As for partitioning, the ability to place tables in a particular tablespace is not partitioning. It might be worth having a separate item for "tablespace support", though. Neilc 00:40, 20 July 2005 (UTC)[reply]

According to this link then MySQL can do partitioning.

That's 5.1. What just came out is 5.0. Turnstep 20:29, 24 October 2005 (UTC)[reply]

MySQL does support functions, but doesn't support external routines...The table should be updated

Cleanup needed

This article needs some encyclopedic context; what is the importance of the software being compared? Why are these particular aspects being compared, and not others? In particular, why the listed operating systems and not more or fewer? Most importantly, what distinguishes a "truly relational database" from a "relational database", other than POV? The existence of [[Comparison of truly relational database management systems" seems odd. —donhalcon^╤ 21:28, 7 March 2006 (UTC)[reply]

See the articles on RDBMS and company for an explanation of the significance of "truly". Turnstep 19:07, 19 March 2006 (UTC)[reply]

Adaptive Server Anywhere

I've removed Adaptive Server Anywhere, as it only appeared in the top table, and a comparison page really needs each item to appear in each section. I could have left it in, but removing it seemed better than adding many rows full of question marks. If anyone has information on it, please feel free to add it back in. Turnstep 19:07, 19 March 2006 (UTC)[reply]

mysql functions

As I keep having to change this, though I'd explain things here: mysql does not currently have an internal procedural language equivalent to pl/sql, pl/pgsql, or transact-sql. What it does have is the ability to create functions by linking to a shared object created using C or C++ [5]. Turnstep 23:19, 12 November 2005 (UTC)[reply]

No — MySQL 5 supports a subset of SQL/PSM, which is functionally similar to pl/sql and pl/pgsql. I've updated MySQL's entry to say it supports "procedures", but not "functions", although I'm not sure there is a universally agreed-upon definition of the difference between the two. Neilc 23:59, 12 November 2005 (UTC)[reply]

Looks like someone changed it back again if you did. I think the section on functions, procedures, and external could use a lot more clarification myself. As it stands, there is not much point in differentiating between the three if enough wiggle room exists for everyone to put a "Yes". :) I'll revert the MySQL/function thing at least. Turnstep 14:16, 7 March 2006 (UTC)[reply]

A function by any other name

Barring any objection, I'm going to merge the Function and Procedure into a single column, as there seems to be no clear distinction made between the two for the purposes of this article, and it just makes the table that much wider without really adding more information. Turnstep 15:45, 7 April 2006 (UTC)[reply]

Add section on XML indexing and other native XML capabilities?

I'm only familiar with SQL Server in this area; can others fill in data for other DBMSs?

GiN

I removed the GiN column, on the grounds that no released DBMS actually implements this feature, even PostgreSQL. Even so, it is just an idea that a few Postgres hackers came up with, it's hardly a proper index type that other databases could reasonably be expected to implement. It might make sense to have a column for full-text indexing, however. Neilc 05:12, 28 August 2006 (UTC)[reply]

Hmmmm ... maybe one column on full-text indexes, and another on on "other" indexes? The Other column would have to be a list or a number rather than yes/no. In addition to GIN, there's GiST (and both of those are algorithms, so other databases could implement them) Spatial, R-Tree, XQuery, etc. Jberkus 19:47, 23 September 2006 (UTC)[reply]

Nobody else has done anything about this, so I'm going to clean it up per my suggestion above. It's silly to have an index type column supported by only one database. Of course, I'm going to need other people to fill in the exotic index types supported by DBs other than PostgreSQL, since I don't know them. Jberkus 07:29, 16 December 2006 (UTC)[reply]

Domain

Not a specialist myself, I'd like to know what the “domain” object is (in paragraph Other objects). Is it data domain? If yes, can anybody add a link to this article, or I can do it myself, as soon as I'm sure it's the right “domain”. --Olivier Debre 19:34, 24 September 2006 (UTC)[reply]

A domain is basically a simple user-defined type: it includes a base type and some optional associated constraints (e.g. NOT NULL, CHECK, foreign key). It's not the same as a "data domain", AFAICS. See CREATE DOMAIN for PG's syntax and some examples. Neilc 20:14, 24 September 2006 (UTC)[reply]

Thanks! --Olivier Debre 10:29, 25 September 2006 (UTC)[reply]

Strange Choice of Databases?

Currently, this page contains the following database systems which I've never heard of before (given 12 years as a DBA) and suspect of having miniscule-to-nonexistant user bases: Pyrrho DBMS, H2, OpenLink Virtuoso, SmallSQL, WX2, SUPRA SQL. On the other hand, it omits the following DBMSes which have had wide usage for years: PervasiveSQL, Progress, MS Access, Adabas and FrontBase (there's probably others, too). Are we aiming to include everything, or are there criteria for inclusion/exclusion? Jberkus 07:46, 16 December 2006 (UTC)[reply]

I think you are right. As well as I wish to ask for somebody to ask RDBMS Linter (Linter Offical Website (Japaneese), Relex Inc. Offical Site, RelexUS site). Information on Relex can be found on those sites. I would do it, but to find all of information would be a bit difficult, even if I do use it. 71.229.243.190 17:52, 22 December 2006 (UTC) (Vmatikov)[reply]