If you are looking for a free or inexpensive database for a software project, you are not alone. Most systems, whether for a software package or a web site, need a database system to store, retrieve, backup or share information. Many applications today use a lightweight database because they can be economical (sometimes free); powerful and meet all the needs of an application. I have started to compile a list of features of three of the top lightweight databases.
| Microsoft SQL Server Compact Edition 2008 | SQLite | Sybase iAnywhere Ultralite 11 | ||||||||||||||||||||||||||||||||||||
| License | If you connect to SQL Server, if you have a processor license, the mobiles do not require a license. If the server is licensed per client, each database that connects will need a CAL (Client Access License). Contact Microsoft. As a standalone database (no synchronization), free to use and distribute. | Free Open Source License. 100% Free, and full source is available. No GPL, LGPL, or any other variation of open source license requirement. | Reasonable Server and Client Licenses. Contact Sybase/iAnywhere for Licensing. | |||||||||||||||||||||||||||||||||||
| Size | 640k* | DB DLL: 261 kb ADO.NET 2.0 Provider: 89 kb |
480k* | |||||||||||||||||||||||||||||||||||
| Max Records/Table | Databases are limited to 4GB; No max number of records | Limited by Page Size: 1073741823; Page size depends on data types and columns per row. | 16 Million | |||||||||||||||||||||||||||||||||||
| Stored Procedures | No | No | No | |||||||||||||||||||||||||||||||||||
| User Defined Functions | No | No | No | |||||||||||||||||||||||||||||||||||
| Custom Collation Sequences | Yes | Yes | Unicode Collation Algorithm, and ICU Unicode support | |||||||||||||||||||||||||||||||||||
| Scalar Functions | Yes | Yes | No | |||||||||||||||||||||||||||||||||||
| Aggregate Functions | Yes | Yes | Yes | |||||||||||||||||||||||||||||||||||
| Locking | SQLite’s locking mechanism is a little draconic and designed for simplicity. Only one writer at a time can be writing to the database. Other writers are blocked until the first writer completes. No readers can be actively iterating a table while a write is in progress, and a write will wait until all readers are finished iterating. 1 |
Row-level locking | ||||||||||||||||||||||||||||||||||||
| Performance | We’ve summarized one informal and non-conclusive study* comparing performance between databases on an emulator and 3 different devices–Treo 750, Treo Pro, iPAQ (note: the lower the number the faster the performance, 1x being the lowest possible for each test):
Please refer to the study for more details: * Mobile DB Performance Testing: with Hardware (ElegantCode.com) Notes: 1. In the table above, the higher the number, the slower the performance. For each test, 1x is the fastest result of the three databases tested and is set as the standard for the other two. 2. SQLite is faster than SQL Mobile, and SQLite’s database files are smaller. |
|||||||||||||||||||||||||||||||||||||
| Replication Support | Yes (SQL Server Merge Replication) | No | Yes (MobiLink two-way replication) | |||||||||||||||||||||||||||||||||||
| Synchronization Strategy | Sync Services for ADO.NET | To merge data from a device to a desktop, you can use the ATTACH statement to attach the PDA’s database file to the master database and provide custom queries to merge changes into the main database. You can also use Web Services from you application to a server and provide your own synchronization. | MobiLink Synchronization Engine | |||||||||||||||||||||||||||||||||||
| Nested Transactions | Yes | No | No | |||||||||||||||||||||||||||||||||||
| Full Portability | Windows Only | Yes | Yes | |||||||||||||||||||||||||||||||||||
| Windows | Yes – x86 and x64 | Yes | Yes | |||||||||||||||||||||||||||||||||||
| Windows Mobile | Yes | Yes | Yes | |||||||||||||||||||||||||||||||||||
| iPhone/iPod | No | Yes | Beta | |||||||||||||||||||||||||||||||||||
| BlackBerry | No | Yes | Yes | |||||||||||||||||||||||||||||||||||
| Google Android | No | Yes | Not Yet | |||||||||||||||||||||||||||||||||||
| Works with .NET | Yes | Yes | Yes | |||||||||||||||||||||||||||||||||||
If you have questions about which database to use in your planned system, please contact the zTeam to discuss your specific needs.