What is the biggest drawback of <your favorite database>?

Question

We all have our favourite database. If you look objectively at your chosen database, what drawbacks does it have and what could be improved?

The rules:

• One reply per drawback with;
• a short description of the limitation, followed by;

• a more detailed description, an explanation of how it could be done better or an example of another technology that does not have the same limitation.

• Do not diss any database that you haven't used extensively. It is easy to take potshots at other technologies but we want to learn form your experience, not your prejudice.

Answer 1

Oracle databases are quite expensive

Oracle does what it does well but the licensing costs are horrendous. That has been improved by the release of Oracle XE but the limitations of that mean that it is a growth constraint on you solution.

Answer 2

PostgreSQL doesn't have a good failover solution, but I understand they're working on it.

Answer 3

Database Microsoft SQL Server

Defect Huge licensing cost

Description

SQL Server has great features and it integrates very well with .NET development. The issue is that when you have to scale up from a shared database to a dedicated database, licensing costs are really high. This, in effect, leads to databases which should really run on a dedicated server, being hosted on shared servers with performance and security issues.

This does not happen with MySQL or PostgreSQL.

Answer 4

Database : Sql Compact Edition

Drawback : Stored procedures are not supported.

Regardless of this limitation, this DB has its' uses especially as a client cache for application that can be smart client or distributed to mobile platforms.

Answer 5

Database Microsoft SQL Server 2005

Defect Badly implemented UI

Description

SQL Server management studio does not offer a great user experience:

• Tabbing behaviour is weird: you are always looking for the right tab
• Keeps on crashing on 64-bit versions
• Missing some features of preceding version, like overview of grants of stored procedures

This does not happen with version 2000.

Answer 6

Database MySQL

Defect Foreign Keys supported only on some table types

Description

Enough said. It has obvious maintenance implications.

From the MySQL manual

Foreign keys definitions are subject to the following conditions:

• Both tables must be InnoDB tables and they must not be TEMPORARY tables.

And here:

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

This does not happen with any other major DB.

Answer 7

Database Oracle

Defect Granularity of grants on packages

Description

You can only grant permissions on packages and not on stored procedures inside packages. Or alternatively, you can grant permissions on single stored procedures but then you put them outside of packages. This requires you to know up front who will use which stored procedure and it is really hard to refactor.

This does not happen with SQL Server.

Answer 8

Database Microsoft SQL Server 2005

Defect Lack of "INSERT OR UPDATE"

Description

Often you need to either insert or update a record in a table, depending on whether the record is present or not. Not having an atomic operation to do so leads to unnecessary transactions.

This does not happen with MySQL or SQLServer 2008.

Answer 9

Database MySQL
Defect Server will start up with damaged tables
Description

If MySQL has a damaged table - from either being killed during a write or some other failure - it will quite happily start up and allow the user to carry on as if the problem does not exist. Granted it will produce some error messages in the log, but from my experience this doesn't help when you're trying to figure out why an application is behaving oddly.

Most other databases will detect and repair the error on startup or simply refuse to start with any sort of corruption.

Answer 10

Database Microsoft SQL Server 2005

Defect Lack of array type parameters

Description

Useful in searches, a lot of times you need to pass a series of values to be matched against. In SQL 2005 you can do a workaround by using CLR inside SQLServer. Given the usefulness it would make more sense to have this feature out of the box.

This does not happen with SQL Server 2008 or Oracle.

Answer 11

Database MySQL 5.0.x and above

Defect Ring replication errors lead to inconsistent data on different nodes

Description

The most serious problem in production we face at the moment is that in a MySQL ring the ring itself produces an error and stops replicating.

Building a ring (or Master-Master-replication) is possible since 5.x.x: You chain the databases in a "ring" so that the replicate data to each other. Every database-node gets all the changes from all other nodes.

We assume that the error lies behind autoincrement- failures. This is known from normal replication, too, but in the new version there are no sufficinet error messages in the error log. I highly recommend not to use this feature in MySQL as long as the problems here are not fixed.

Answer 12

Database PostgreSQL

Defect No SQL Profiler

We asked the developers about this at a recent conference and I understand it's now something they're looking to implement.

Answer 13

Database Postgres

Defect No analytic queries

Description

Analytic queries, introduced by Oracle, are part of the SQL 2003 standard. Unfortunately Postgres hasn't implemented them yet.

Answer 14

Database Oracle

Defect Did not handle long datatype well for too long

Description

Oracle only had the long datatype until 9i (I believe) at which point it was deprecated in favor of the LOBs. There is a ton of code out there, however, which still has longs and all of the related restrictions. The biggest of which was that each table could only have one long column and it had to be at the end of the columns. See here for a more exhaustive list of restricitons on the long.

Answer 15

I love the flexibility of sequences in Oracle as compared with other databases autoincrements, but the inability to set seq.nextval as a default for a pk column is somewhat annoying, and must be trivial to fix.

Answer 16

Database Oracle

Problem Temp table definitions are not private

Description Many databases (eg Postgres and Sybase) allow you to create temp tables on the fly, insert into them, add indexes if you want, then query from them. Oracle has temp tables, but the temp table definitions exist in a global name space. Therefore the temp table has to be created by a DBA, you need to synchronize between the table definition they used and your code, and if two pieces of code want similar (but not identical) table definitions, they need to use different names. These differences make temp tables far less convenient for developers.

Yes, I understand the benefits for the query optimizer of having global definitions. However for me the lack of convenience makes Oracle's temp tables virtually useless for me, while I use them very intensively in Postgres.

Answer 17

Database : PostgreSQL

**Problem : ** is that connector for C# for example are not really up-to-date and crash with advanced feature.

Answer 18

Database: Oracle

Problem: The names of tables, procedures, columns, etc cannot exceed 30 characters. This is infuriating.

Problem: It's slapdash JDBC compliance. For example, stored procedures do not return results sets in a JDBC-compliant way, but instead of a proprietary OUT parameter type. This means you can't use higher-level JDBC abstractions.