I am moving the climate project over to SQL Server, which seems like a great opportunity to compare and contrast a commercial DBMS giant (SQL Server, albeit the Express version) with the open-source MySQL. I think that there is a misconception out there that open-source products are “toys”, not up to the task of professional, commercially-important use. However MySQL is definitely a professional-standard DBMS package used by companies like Yellow Pages, Google and Facebook – so it seems timely that I put my open-source-vs.-proprietary prejudices aside and consider these two from a purely objective standpoint.
The capabilities of SQL Server 2012 (including SQL Server Express 2012) are categorised in full at MSDN.
MySQL Capabilities (that relate to climate project) are below:
On the surface, it seems that either product will be more than able to handle the climate chip database. It is interesting that both products have limitations on the RAM, and while this makes sense for SQL Server Express (there has to be a reason why you would pay for the full version), I am surprised that MySQL is limited to 2 GB of RAM.
MySQL Foreign Keys: While not a problem for the climate chip’s single table database, it is worth noting that the default MyISAM database engine does not currently support foreign keys. It will accept foreign key references in a CREATE TABLE statement, but it will not implement them. However, you have the option of the InnoDB engine for this. For more information, see these links:
- MySQL InnoDB Foreign Keys (MySQL documentation)
- MySQL documentation on Foreign Keys
- A blog discussion on MySQL and foreign keys
SQL Server Indexes
I need to look into this a lot more. On the surface, it seems that SQL Server only supports B-Tree indexes. The current climate database uses a B-Tree index on (latitude, longitude) however, as we are essentially only performing lookups, I have proposed that a hash index would provide better lookup performance. Questions to research:
- Can SQL Server support hash indexes?
- If not, how does SQL Server implement key-value stores?
- Do I need to re-address the index structure and simply work on optimising a clustered B-Tree index?