Duelling databases: Four apps tested
Even skimming the surface of their capability is a daunting prospect. To test them completely would require not only complete and established infrastructure but ways of simulating workloads, demands, data types, queries, and so on.
Because of the great diversity of database, some context is undoubtedly required. Our scenario in this comparison calls for a database solution for a relatively small e-commerce company with less than 200 employees. The company sells DVDs and books over the Internet and will initially have around 1000 customers and an online inventory of 50,000 items.
Like all companies, this one has plans for future growth and would like to see a customer base in the hundreds of thousands, and would like to diversify their inventory to the point where millions of items are available online.
The database must look after their stock, customer lists, and seamlessly integrate with their accounting system for billing and purchasing.
Initially the database must run effectively on a dual processor or four-way server but must be able to scale up to a small server farm should the demand exist. The company does not want to entertain solutions whose only scalable path is for very expensive multi-processor servers greater than four-way. For our testing we installed the databases on a Dell PowerEdge 6600 with Quad Xeon processors and 4GB of memory.
Cost of the initial server and database software is certainly an issue, so ideally the company is looking for software that is capable of scaling up with the addition of more hardware, or has a clean and simple migration path towards more powerful enterprise versions of the software.
Obviously ease of configuration and administration is paramount -- the in-house staff must be able to easily enter and update data, while the novice administrator must be able to make minor changes to the database on the fly.
This being our first foray into this product category, we tried to keep it simple, and decided to look at the entry-level databases from a handful of vendors including Oracle, Microsoft, IBM, and MySQL. That said, already MySQL's product is in a different category to the rest because it doesn't come in "light" versions -- it was included because it is free. While there are other popular database products available, such as Sybase, we will save a full comparison for another time.
Note: At the time of this review Microsoft was in the midst of updating its database portfolio, and only the Beta version was available, and just prior to printing Oracle released a new database that is free of charge.
What is it?- Database: A database is a collection of related information stored in a structured format.
- Relational Database: A collection of "related" databases, for example and travel agent booking system consisting of customer, airline and account details, can be combined and would be referred to as a Relational Database.
- Record: A database comprises of records and each record contains all the information about a single member or item.
- Field: A record comprises of fields where each field is a single piece of information relating to the record for example your Medibank record may contain individual fields for first name, last name, Medibank number, date of birth, etc. All records have the same structure and so contain the same fields but obviously each field has its own unique data.
- DBMS: Database Management System.
- RDMS: Relational Database Management System.
- Constraint: A constraint is a property assigned to a column or the set of columns in a table that prevents inconsistent data values from being placed in the column/s. They are used to enforce data integrity and therefore database reliability. There are several types of data integrity including Entity, Domain, Referential and User-Defined.
- Dirty read: If a database allows a user to read updated data that has not been "committed" by the user inputting the data then this is called a dirty read. A database should only allow data to be read that has been committed.
- Query optimiser: When you perform a report or query the optimiser is responsible for determining the fastest and most efficient way to perform the operation. Often the query optimiser relies on table and index statistics to determine the best way to optimise the operation.
- Primary key: The Primary key is a field that uniquely identifies each record in the table and is often an item that is unique for each record in the database such as you drivers license number for example. The database can generate a primary key itself if you require it but it makes more sense to use an existing unique field. A foreign key identifies records in an external table.
- JDBC: JDBC is an API that is included in J2SE and J2EE releases that provides cross-database connectivity to a broad range of SQL databases and even provides access to other data sources, such as spreadsheets or flat files.
- Stored procedure: SQL commands are compiled into a stored procedure that can be called and executed repeatedly saving time and boosting performance.
- Triggers: A trigger is an action that automatically takes place when a certain database event occurs, the action could for example be a stored procedure that "fires" when a trigger condition is satisfied.
- OLAP: OLAP is an abbreviation for Online Analytical Processing and is a category of tools that provides analysis of database data. This enables users to analyse different dimensions of multidimensional data and can for example provide trend analysis.
- ODBC: ODBC is an abbreviation for Open DataBase Connectivity, this is a standardised method for accessing databases developed in 1992 by the SQL Access Group. ODBC makes it possible to access and exchange data between any compliant database systems.
- ACID Transaction Management: ACID is the cornerstone for DBMS and is an abbreviation for Atomicity, Consistency, Isolation, and Durability. Obviously if you are attempting to purchase a DVD online at the same time another user is attempting to purchase the identical DVD you would want to ensure your transactions were independent and distinguishable with no overlap to ensure a reliable and accurate transaction occurs.
- Entity integrity: Refers to the fact that there are no duplicate rows in a table.
- Domain integrity: Ensures valid entries in a column by restricting the type, range or format of the data values possible.
- Referential integrity: Where row data is shared between more than one record this ensures that the data cannot be deleted while there are still dependent records.
- User-defined integrity: You may have your own business rules that do not fall into the other data integrity categories, these can be enforced by "User-Defined Integrity"
Ensuring the database performs well is not as simple as it sounds, and can appear a black art to the uninitiated. Unless you know what you are doing, a self install is probably a bad idea. Based on your own scenario, most vendors will be able to suggest settings that will improve the performance of the database appreciably over its standard out-of-the-box configuration. We did find, however, that some of the products include Wizards that did a good job of optimising the database by asking the user to select simple descriptions of their database type and usage patterns.
Security will always be a very important consideration given that the database will contain sensitive customer information such as credit card details. Not only is it important that the database be encrypted, it is also important to provide an audit trail to ensure there is no abuse of privileges by "authorised staff".
The front end for the database in this scenario would include data consistency and bounds checking but the database should also support robust integrity checking of its own.
What follows is not a detailed breakdown of the databases tested but rather a highlight of some of the more unique features of each vendor's product.
SQL Server Express is one of two free databases we tested and is actually Microsoft's replacement for its earlier free offering the Microsoft Desktop Engine (MSDE) which was based on the old Access technology. Express is a complete redesign and meets HIPA requirements with robust security features. There is one particular advantage over MSDE that should be noted and that is Express no longer has a "governor" that would kick in with MSDE and throttle back performance.
| Product | Microsoft SQL Server Express (Beta2) |
| Price | SQL Server Express is free to use and redistribute. End users must agree to the Go Live licensing to distribute this release of SQL Server Express Edition. |
| Vendor | Microsoft |
| Phone | 13 20 58 |
| Web | www.microsoft.com.au |
| Interoperability | |
| Limited to one CPU and single user thread, good features set, very good user interface, solutions only for Windows. | |
| Futureproofing | |
| Very limited capacity when compared to the other solutions straight out of the box. However, scaling up to Enterprise versions of SQL Server is straighforward. | |
| ROI | |
| Admittedly free but has limited features compared to the other offerings. | |
| Service | |
| Support includes e-mail with a one day turn around and Mon-Fri 9am to 5pm telephone support. Extended support plans are available. | |
| Rating | |
Perhaps the biggest limitation is that Express only scales to several dozen users, at which point Microsoft recommends upgrading to SQL Server Standard Edition. That said, you could support up to 1000 users as long as they did not place too much load on the database. What Express does not include when compared to SQL Server SE and above is Analysis Services, Reporting Services, Data Transformation Services, and Notification Services but some users would argue that these features are crippleware anyway.
Then, of course, there is the database size limit of 4GB. Now this does sound quite large, and for a pure text-based database it would be massive when compared to the requirements of our fledgling company scenario. However, we are talking online purchasing and purchasers are going to want to see pictures and specs of what they are buying, which in turn can bloat out a database severely.
In other ways the database competes quite well with the competition in terms of index lengths, 32 nested procedure levels, 1024 columns per table, triggers, and no limit on the table row length. To log into the database you can choose to simply go with Windows Authentication or "Mixed Mode" which is a mix of Windows and SQL Server Authentication. Express supports data encryption and provides audit trails. We found we were unable to connect at all with the database when we tried to use Windows Authentication, so we reinstalled with the Mixed Mode option and all was fine.
Express has Native XML support, integrates with Visual Studio so that developers can add data and query databases within Visual Studio -- it is through Visual Studio that users are able to create reports with tables and charts. A neat feature is "Xcopy" that makes it simple to move databases from one location to another.
The format of SQL supported is Transact-SQL and Express utilises "Snapshot Isolation Level" to ensure users only read consistent and committed data.
There is no denying that SQL Server Express is the weakest of the databases in this group but it is free and if funds are particularly tight it may just help your start-up company get off the ground. It should cope with the early days of a small e-commerce site and when the company begins to hit the limits of Express it is a simple matter, although more costly to migrate the database over to SQL Server Standard Edition for example. As is expected, it only runs on a Windows platform.
Perhaps MySQL is best known for two reasons: firstly it is open source and perhaps the clincher for most, it is free. Now where other vendors offer a free version of their software, it is inevitably a nobbled version with support for only a single CPU, memory restrictions, database size restrictions, or they include a performance throttle to ensure sales of the higher end products from the same vendor are not eroded.
| Product | MySQL V4.1.14 |
| Price | $0 without support, USD$595-4995/year MySQL network subscription depending on support requirements. No limitations on DB size, # of users, amount of CPUs or RAM. |
| Vendor | MySQL AB |
| Phone | 02 8307 3824 |
| Web | www.mysql.com |
| Interoperability | |
| No limit to the number of CPUs, very strong features set as provided, very good user interface, solutions for Windows, Netware, Mac, Unix and Linux. | |
| Futureproofing | |
| Brilliant scaling capabilities "as is" with no need to scale up to Enterprise versions as you do with other vendors, clustering as failover is natively supported. | |
| ROI | |
| Excellent value -- it's free but is not lacking in features or power. | |
| Service | |
| Support, warranty and IP indemnification available through MySQL Network subscription, from basic (US$595) to 24 x 7 x 365 w/30min response time (US$4995), simply per server per year. Directly by expert support/development staff. Non-GPL licences also available, as well as training, certification, and consulting. | |
| Rating | |
MySQL is free but if you hanker for the arguable security of a formal support contract it will cost you from US$595 to US$4995 a year depending on your requirements. Refreshingly, the support cost is not tied to the number of CPUs or users -- you could run a massive data warehouse or online transaction system and pay $0, or up to the US$4995 (per server) limit for support as you chose. Many, of course, chose the $0 path as the support from the MySQL community is extensive -- you would have to be pretty unlucky to come across a problem that these guys have not personally experienced or could not solve. It is worth noting, however, that if you develop a commercial product based on MySQL that you intend to on-sell, then there is naturally a fee involved.
After we completed our testing version 5 of MySQL was released so we have updated the features table to reflect the latest version's features. In summary, some of the new features in version 5 are Stored Procedures, Triggers, Archive Storage Engine, and the Federated Storage Engine which creates a single logical database across many physical servers.
MySQL runs the entire gamut in terms of hardware supported and can reside in as little as a 64MB Flash Card and 16MB of RAM as an embedded application all the way up to multi-CPU servers and clusters thereof -- and cluster support is comprehensive with full fault tolerance.
It's not weak in terms of database size either; it has a 64TB limit per table and a quick glance at the features table will show that with most features MySQL goes head to head with the other vendors' products. Indeed it is probably a little unfair comparing MySQL directly with the low-end products from the other vendors as MySQL has been built to compete at the high end with the other vendors' enterprise-class databases. And because your initial software install can support a small database or a large enterprise-class database there is no software upgrade needed -- the only extra expense will be the hardware platforms to support your requirements.
Server OS support is broad -- distributors claim that you can install MySQL on any Unix, Linux, Windows, or Netware environment.
While version 4.x of MySQL did not trap data input errors, for example alpha characters in a date field, and only issued "warnings", version 5 now has a strict mode that rolls back the transaction if a violation is encountered. Row locking is implemented in memory but apparently the implementation is so memory efficient, requiring just a couple of bits per row lock, that there are no incidents of row lock escalation. Dirty reads are only encountered if you explicitly set the isolation mode to "read uncommitted".
Installation on Windows Server 2003 was very fast and configuration simple, even for a novice, with a clearly navigated configuration wizard. During configuration the installer is presented with simple options describing the server and its usage over nine or so screens, and at the end of it all your server's parameters are configured without needing to know a single CLI (command line interface) command.
At first sight it appears to the uninitiated they need to rely on the CLI to administer the database, which is pretty daunting given that we have been subjected to many years of GUIs as the front end for everything from operating systems to applications. Admittedly there are not all that many commands to remember and the syntax is, well relatively logical, and of course if you watch anyone versed in SQL they will forgo any GUI most of the time and just hammer away in the CLI. However for our scenario we are envisaging, in the early days at least, the role of database administrator (DBA) falling on one of the existing staff as a part time role, ie, someone who won't be eating, breathing, and sleeping with SQL so a GUI would be a welcome grief saver.
Just two short downloads are all that is needed to get your hands on MySQL Query Browser and MySQL Administrator. Now I must admit that I thought the GUIs would look a little home baked but this is definitely not the case.
Administrator, for example, while perhaps not as extensive in features as Oracle or IBM's DB2, is nevertheless extremely user friendly and very simple to navigate with its bold graphics. While not as feature packed, it nevertheless has all the functionality a novice DBA is likely to need -- and more. From here you can administer users, configure and optimise the database, backup and restore databases, and check on the "health" of the database.
The Query browser is similarly featured and makes short work of creating and modifying tables, building and managing queries, and debugging SQL scripts.
Should you need to migrate a database from another proprietary database such as MS SQL Server, Oracle or MS Access then the Migration Toolkit, again with the friendly GUI, should take a good deal of the worry out of the process.
After exploring Oracle 10g Standard Edition it is no surprise that when asked to name a database Oracle is probably the first vendor's name to roll off most lips. Sure it isn't cheap but even the Standard Edition (SE) is packed with features that makes management, usability, and scalability very simple and straightforward.
| Product | Oracle 10g Release 1 Standard Edition |
| Price | Per CPU, unlimited users $19,814 (+ software update and support $4358.97) or, per named user $396 (min 5 users) (+$87.18 per user for software update and support). |
| Vendor | Oracle Corporation |
| Phone | 1300 366 386 |
| Web | www.oracle.com/au |
| Interoperability | |
| Limited to four CPUs, strong features set, very good user interface, solutions for Windows, Unix, and Linux. | |
| Futureproofing | |
| With up to four CPUs this edition will handle a large workload before the need to upgrade. The product cleanly and painlessly scales up to Enterprise Edition and support for clustering, failover, etc. | |
| ROI | |
| Relatively expensive when compared to DB2 but does include features and capabilities that are added cost with DB2. | |
| Service | |
| Support licence (once purchased) covers all software patches and upgrades and 24x7 technical support by phone or e-mail. | |
| Rating | |
The upgrade path is very smooth and simple so if you needed to upgrade from SE to the Enterprise Edition you simply install the software and do not need to make any changes to the database, applications, or administration.
Installation is a painless procedure although configuration and optimisation would be a difficult for the uninitiated. To be fair this is not because configuration is made difficult, it is simply the sheer breadth and scope of parameters that can be configured, and it is hard to see how Oracle could have made the task any easier than it is already with the easy-to-navigate user interfaces.
Hardware requirements are surprisingly modest at 1GB of memory and 2GB of disk space, although the latter does include space for your data. The SE version can handle up to quad-processor servers but this can get quite expensive depending on the pricing model you choose. For an internal company database the specified number of user model will probably be more cost effective but then in an online e-commerce environment. Like our scenario company, you cannot specify the users so Oracle charges per CPU in your server.
If you are starting off with a modest workload we advise you hold off loading up your server with multiple CPUs until you need them because a four-way server will cost four times as much to licence than a single CPU.
Oracle 10g SE supports Windows, Linux, Solaris, HP OpenVMS, Mac OS, AIX, IBM z/OS, and HP Unixââ,¬"just about every flavour with the exception of Netware.
As for features, SE gets a tick against almost every feature you would want, including unlimited table row lengths.
Data integrity is obviously critical to any business and this product guarantees users will never experience "dirty reads" through the use of the Multi Version Read Consistency (MVRC) mechanism. That is not to say the other databases serve up incorrect data but with some database engines when the workload is high, uncommitted data can be flushed from buffers to disk potentially creating a dirty read. MVRC also ensures that readers do not block writers and visa versa.
SE also doesn't escalate row-level locking which can occur if your locks are maintained in memory and during a busy time the memory starts to fill. In this case some database engines lock, the buffer can fill and force an escalation to free up memory to maintain locking, albeit at a higher level. Rather than maintain a lock buffer Oracle SE instead flags the relevant row to indicate a lock or unlocked state and so can, in theory, maintain an unlimited number of locks.
Another neat inclusion is Oracle Spacial which can analyse the special relationship of associated data, for example, the closest warehouse for a particular item of stock for a retail store.
Administration is via Enterprise Manager (EM) which we found very easy to use and extremely powerful -- it's a close tussle between EM and IBM DB2's Control Center for the best interface honours. If there is an item you need to tweak, view and load, or alter, it's almost certain there is a link or button to it in EM.
As previously mentioned, Standard Edition is not cheap, it is actually the most expensive product in the group at almost AU$20,000 per CPU and over AU$4000 per year for support. However, we should note that we were constrained to test Standard Edition because our test rig was equipped with four processors. Perhaps a better option would be Standard Edition One which is licensed for a maximum of two CPU's and is a lot less expensive at AU$6598 per CPU and AU$1451.54 per year for support.
Of course, if you can nominate your users the cost can be significantly lower. For a small number of users, for example, Standard Edition is AU$396/user and AU$87.18/user/annum for support, while Standard Edition One is AU$197/user and AU$43.30/user/annum support; in both cases there is a five-user minimum. Support includes all software patches and updates and 24x7 support.
- Introduction
- What is it?
- SQL Server Express
- MySQL
- Oracle 10g
- IBM DB2 Express
- Specifications
- Editor's Choice
DB2 Express is at the very bottom of the DB2 database family which includes Workgroup Server, Workgroup Server Unlimited, Enterprise Server, and Enterprise Server DPF.
| Product | IBM DB2 8.2 Express Edition |
| Price | per CPU, unlimited users $8,106, or per server $1,037.84 plus $206.24 per user. |
| Vendor | IBM Australia |
| Phone | 13 24 26 |
| Web | www.ibm.com |
| Interoperability | |
| Limited to two CPUs, strong features set, very good user interface, solutions for Windows and Linux. | |
| Futureproofing | |
| This edition can handle quite a workload before you would need to move to a more powerful version. Scales with little drama all the way up to Enterprise Edition and support for clustering, failover, etc. | |
| ROI | |
| Lower cost than Oracle but Express only supports two CPUs to Oracle's four and some features are cost options. | |
| Service | |
| Support is included in the purchase price for first year and includes 24x7 phone and e-mail support. At the end of the first year the customer has the option to renew the licence at a cost of $1,600/year/CPU or if they chose pay less for shorter support hours. | |
| Rating | |
Be careful when examining the features of the product as we found DB2 boasts many features but not all of them are standard -- in fact, quite a few are optional extras or not even available. For example, partitioning the database across multiple servers for performance and redundancy is only standard on Enterprise Server DPF.
Installation and configuration are very easy and the novice can simply walk the logical and simple path of the DB2 Wizard -- the Wizards often present multiple choices for database creation and configuration that are clearly explained and carry out much of the many and tedious configuration settings in the background. For example, during configuration we were presented with an option to tune the database performance based on usage model. Three models were presented: a transaction model, a data warehouse model, and in case these two are too confusing or if you have a mix of both in your environment, there is a third option that encompasses both.
There is no doubt that IBM's DB2 Express database features the most extensive and easiest to use file import and compatibility capabilities. The import wizard is simple to use and the import characteristics of the file can be finely tuned through a standard GUI that any novice could drive.
Another feature worth a mention is the Database Federation feature (which comes at extra cost). In a nutshell, Federation utilises "wrappers" to allow your front end to query other vendor's database applications. For example, you may have a stock management system on Sybase and an employee HR system on SQL Server -- with the corresponding wrappers DB2 Express allows your front end to seamlessly query any of the databases. The software is even smart enough to re-write SQL commands to speed up performance across the different databases.
While DB2's 512GB per table may appear a little small when compared with MySQL's 64TB for example -- you can have up to a "theoretical limit" of 32,768 tables or 16,384TB in total.
While Oracle's row locks are inviolate, the isolations levels of Express can be tweaked between allowing dirty reads (when a database allows a user to read updated data that has not been "committed" by the user inputting the data) and not allowing dirty reads with the former providing a boost in overall performance -- which path you follow depends on your requirements.
Express' lock list is maintained in RAM in contrast to Oracle's table-stored locks so in high and unusual load situations it is possible to overload the lock list buffer. This can potentially cause row locks and can escalate up to page and table locks in extreme circumstances. If this happens frequently it is obvious that you have too little RAM, but if it only happens on rare peak load conditions then you have to make the choice and weigh the added expense of the rarely utilised memory and user satisfaction. It's a sad fact that if it is busy and the locking escalates, transaction speed can plummet as greater slabs of the database are unavailable.
A feature of Express' excellent user interface is the Health Center which tracks database performance and issues Attention, Warning and Alarms in escalating severity. It is displayed in a simple graphical format where the user can drill down and investigate every instance. Hand in hand with this feature is the Design Advisor which deeply analyses the database's operational statistics and then recommends tweaks and improvements to optimise the database. This may range from advice on implanting extra indexes or dropping unused indexes, to repartitioning suggestions.
Speaking of the user interface, at first glance it doesn't appear as friendly as Oracle's but it turns out to be very easy to use and drill down. It is very powerful with a wide range of features and 14 highly desirable Wizards.
The first year of support is included in the purchase price, known as Passport Advantage Express it includes 24 x 7 phone and e-mail support. At the end of the first year the customer has the option to renew the licence at a cost of AU$1600/year/CPU or if they choose, pay less for shorter support hours.
Like Oracle, IBM has two licensing schemes. The first is based on a fixed cost per server, no matter what its capabilities, and then a cost per user. This of course would not work for an online system where the number of users is not quantifiable, so you are left in our scenario with a charge of AU$8106 per CPU and unlimited users. This appears quite reasonable although bear in mind that a lot of features that are standard with Oracle are extra cost items with DB2.
Specifications| Product | IBM DB2 8.2 Express Edition | Microsoft SQL Server Express (Beta2) | MySQL V4.1.14 | Oracle 10g Release 1 Standard Edition |
| Company | IBM Australia | Microsoft | MySQL AB | Oracle Corporation |
| Phone | 132 426 | 13 20 58 | 02 83 073 824 | 1300 366 386 |
| Web site | www.ibm.com | www.microsoft.com.au | www.mysql.com | www.oracle.com/au |
| Price as per test scenario | Per CPU, unlimited users $8106, or, per server $1037.84 plus $206.24 per user | Free | $0 without support, US$595-4995/year MySQL network subscription depending on support requirements. | Per CPU (unlimited users) $19814 (+ software update and support of $4358.97 per annum), or per named user $396 (minimum of 5 users) (+ $87.18 per user for software update and support per annum) |
| Number of CPUs supported | Maximum of two | Single CPU (single user scheduler thread) | Unlimited (dependent on OS/hardware) | Maximum of 4 -cores" |
| Maximum buffer pool memory size | Around 3.5GB | 1GB | Limited by OS. MySQL utilises multiple buffer pools for different purposes, each individually tunable. | Restricted to the hardware size |
| Maximum database size | Theoretical limits: 512GB Per Table space , maximum 32768 tablespaces, practical limit would be smaller | 4GB | 4GB-64TB per table depending on storage engine, no per-DB/server-wide limits (depending on OS). | Restricted to the hardware size but indicative values are: Win 32-bit: 2.75GB or 64GB with memory extensions. Theoretical limit 140TB. |
| Hardware requirements (recommended, CPU, RAM, Disk) | Recommended for 50GB to 250GB of data: 100GB to 500GB of disk space, 3GB to 4GB RAM per CPU, One or more 2.5GHz CPUs | 1 GHZ processor, 512 MB, 425 MB disk space recommended | Anything from for instance 64MB Flash card + 16MB RAM for embedded boxes, to multi-CPU multi-GB environments. | 1 GB RAM, 2GB Hard Disk space for Database. |
| OS supported | Windows, Linux | Windows | Any Unix, Linux, Windows, NetWare and Mac OSX. | Windows, Linux, Solaris, HP OpenVMS, Mac OS, AIX, IBM z/OS, HP Unix |
| Computing Cluster Support | No | No | Yes | Yes |
| ANSI SQL Compliant | Yes | Basic | Extended subset of SQL-99, plus SQL-99 and SQL:2003 features. | Yes |
| Memory Management (auto) | Manual/auto | Yes | See buffer pool info. | Yes |
| Server managed backup and recovery | Yes | Yes | Yes | No |
| Legato Storage manager compliant | Yes | NA | NA | |
| Multiple block size support | Yes, 4,8,16,32K Page | No | Yes, with recompile. | Yes |
| Triggers (eg before, after, instead, DB event) | No cascade before, after, instead of, insert , delete , update | After, Instead of, DDL | MySQL 4.x (As tested) no, MySQL 5.0 for each row triggers, before/after. | Yes |
| Stored procedures | Yes | Yes | MySQL 4.x (As tested) no, MySQL 5.0 supports SQL:2003 stored procedures and functions. | Yes |
| Index types supported | Type1, Type2, Clustering, Multi Dimensional Clustering, spatial index with spatial extender | Btree, XML | Dependent on storage engine and column type used: Btree, Rtree, Hash, T-Tree, FULLTEXT. | B-Tree, Bitmap, Function-Based, Partitioned |
| Array support? | Yes | Not in t-SQL, however yes in client. | No | VARRAYS Supported |
| User defined functions (list) | Yes | Yes | Yes | N/A |
| Column name length | 30 | 128 | 64 (>512 for aliases inside queries) | 30 |
| Index name length | 128 | 128 | 64 | 30 |
| Table name length | 128 | 128 | 64 (>512 for aliases inside queries) | 30 |
| Constraint name length | 18 | 128 | As column names | 30 |
| Max char() size | 254 | 8000 | 1048543 | Column size-2000, Variable size-32767 |
| Max varchar() size | 32762 | 2GB | 1048543 | Column size-4000, Variable size-32767 |
| Max columns per table | 1012 for 32K page table | 1024 | 2599 | 1000 |
| Max table row length | 32772 for 32K page table | none | 65534 + any text/blob cols up to 4GB each | Unlimited |
| Max columns per index | 16 | 16 | 16 | Bitmap-30, Others-32 |
| Max indexes per table | 32622 or storage limit | 249 non clustered, 1 clustered | 61 | Unlimited |
| Max nested stored procedure levels | 16 | 32 | Limited only by thread stack (OS/configurable) | 254 (Unlimited subqueries in the FROM clause of a top-level SQL query) |
| Password Management | Yes | yes | Yes | Yes |
| Native Data Encryption | Yes | Yes | Yes | Yes |
| Auditing Features | Yes | Yes | Yes | Yes |
| Java | Yes | Yes | Yes | Yes |
| JDBC | Yes | Yes | Yes | Yes |
| XML | Yes | Yes | Yes | Yes |
| ODBC | Yes | Yes | Yes | Yes |
| Microsoft Transaction Server | Yes | Yes | Yes | Yes |
| OLE DB.NET & ODBC.NET | ODBC.NET | Yes | Connector/Net | Yes |
- Introduction
- What is it?
- SQL Server Express
- MySQL
- Oracle 10g
- IBM DB2 Express
- Specifications
- Editor's Choice
Oracle 10G and DB2 are acknowledged as industry leaders in the database field and both are strong in terms of features and are surprisingly easy to use with clean GUIs.
However, one of the other databases tested is also acknowledged by the industry and has proven itself in terms of reliability and performance in many very large installations around the globe. And what's more it is free!
Release 5.0 of MySQL is really taking it to the Oracle and DB2 with advanced features such as cluster support and fault tolerance and in most other departments the features run head to head with the competition. Non-SQL junkies can take heart with the GUIs dramatically reducing the reliance on the CLI, bringing administration and configuration within the realms of the novice. MySQL V5.0 is a compelling product and it is hard to argue against its nomination for the Editor's Choice award.