9 reasons why I prefer MySQL to MS SQL Server

Posted on by Lea Verou

In the past, I used MySQL for any of my DBMS needs. It wasn’t really an informed decision based on solid facts, actually I had never really given it any thought. It was what most developers used, it was what vBulletin used (one of the main projects of my company is based on vBulletin), it was what most hosts had pre-installed, in other words, it was the popular choice and I went with the crowd.

Unlike most decisions taken that way, this one turned out to be correct (so far at least). In the university where I study (yeah, I do that too occasionally :P ), there is a great and extremely useful class on Database Systems offered in my semester. The only drawback is that it’s done on MS SQL Server. Consequently, I had to work with it quite a lot, and my conclusion was that MySQL is far superior (mostly syntax-wise as I don’t have the deep knowledge required to judge them fairly for other things, so don’t expect a deep analysis about performance or security – as far as I’m concerned, they are equally good at those). Here are a few reasons:

  1. No ENUM datatype. Yeah, of course I can define a column with a char/varchar type and add a constraint to only allow for particular strings, but this kinda defeats the purpose of memory saving that the ENUM datatype in MySQL offers.
  2. No INSERT IGNORE. Instead you have to go through hell to simulate that in MS SQL Server.
  3. I hate it that I can’t use “USING(columnlabel)” in a JOIN query and I have to use “ON(table1.columnlabel = table2.colmnlabel)” all the time. Yeah, I know that the first one isn’t standard, but it’s shorter, cleaner, more elegant, and …you can still use “ON(…)” if you don’t like it. Having more options is never bad, is it?
  4. With MySQL you may insert multiple rows at once elegantly (“INSERT INTO tablename (…), (…), …”), without using the “INSERT INTO tablename SELECT (…) UNION ALL SELECT (…) UNION ALL …” hack. Moreover, the elegant MySQL way also happens to be the standard, a standard that SQL Server doesn’t follow.
  5. Triggers can only run per statement, and not per row. This isn’t really important, since for most cases, it’s more efficient to define a per statement trigger anyway, but it doesn’t do any harm to have an extra option, does it?
  6. Paging is dead-easy on MySQL: SELECT * FROM foo LIMIT 10,20 . With MS SQL Server you have to jump through hoops to do the same thing, especially if your query is not trivial.
  7. In MySQL, when you want to convert an integer to a hex string, you just call HEX(). In SQL Server you have to call an undocumented function and do some string manipulation to do the exact same thing.
  8. MySQL runs on every platform, whereas with MS SQL Server you’re stuck with Windows.
  9. Last but not least, MySQL is free (and when it’s not free, it’s at least cheap) and opensource :-)
  • Andy Edinborough

    Legitimate gripes, but …
    #4 MS SQL Server 2008 supports multiple inserts
    #6 In .NET development, paging isn’t done in the SQL. It’s done through the data adapter.
    #8 Sure MySQL will run on Windows, but you lose stability and performance … and not because of Windows :]
    #9 MS SQL is just as free as MySQL. They both have “Community”/”Express” editions, but if you want to get to the enterprise level, they are both very expensive.

    MySQL has it’s flaws too, but I won’t get into that.

  • http://leaverou.me Lea Verou

    Hello Andy!
    #4 Oh, that’s great, I didn’t know about it :)
    #6 I have no idea about .NET development. I’m talking about JSP and PHP.
    #8 At least you have a choice ;-) For instance, you don’t need that much stability and performance in your local server that you use for development. So you may use MySQL on that even if you run Windows, and then have Linux on the actual production server. With MS SQL Server you have no choice.
    #9 Equally expensive?

    By the way, of course MySQL has it’s flaws, and actually there were a few things I liked about MS SQL Server in comparison to MySQL. The things I didn’t like though were more.

  • http://john.katsiotis.com John Katsiotis

    #3, #4, #6, #7 those imho concern the software that u use and not the core of the DBMS.
    For example : You could write a plugin for Management Studio that translates USING to the one you want and the execute the query. Of cource it’s better to have out of the box functionality.
    So when you re building a small/medium scale application it’s is not that important what you will choose. All perform pretty well. But when you need something more advaced? Let’s say replication, security at column level etc, MS SQL (or perhaps Oracle) is a better solution from MySQL.
    Also keep in mind the Guid feature provided by MS and supported by SQL Server!

  • http://leaverou.me Lea Verou

    Of course you could, although if that functionality is included out of the box, then I guess the DBMS will be able to optimize these queries more efficiently, right? :)

    Welcome to my blog by the way :)

  • Joel_MMCC

    Another cool thing in MySQL that it took MS SQL until version 2008 to get an equivalent of:

    INSERT … ON DUPLICATE KEY UPDATE … lets you handle the situation whereby some data may or may not already exist, and if it does, you want to update it, but if it doesn’t, you want to insert it. This is reasonably commonly needed functionality.

    MS SQL 2008 implements the newest SQL standard for this functionality, namely, the (truly cool) REPLACE statement. But prior to that, even in MS SQL 2005 (let alone 2000, 7.0, 6.5, 6.0, and all prior versions), you’d need either an IF EXISTS (…) BEGIN UPDATE … SET … END / IF NOT EXISTS (…) BEGIN INSERT (…) VALUES (…) END _or_ IF … IN (…) BEGIN UPDATE … / IF … NOT IN (…) BEGIN INSERT … _or_ an INSTEAD OF … Trigger to do that in pure SQL, or else have your client logic handle it.

    MySQL has much more powerful data formatting capabilities out-of-the-box. For instance, you could easily format a datetime column as, for instance, “Monday, June 22nd, 2009 @ 12:34 pm” (or even, if you know you’ll be outputting to HTML, “Monday, June 22nd, 2009 @ 12:34 pm”) using a single SQL function call. With MS SQL 2005 and later, you could, if you have sufficient permissions, write a CLR (Common Language Runtime) function in any supported .NET language that leverages .NET’s quite powerful formatting API, and get powerful formatting functionality as an SQL function, but there is none built-in with even remotely that level of functionality.

  • Joel_MMCC

    Ooops! In my previous example, it stripped my HTML code. That second date should read:

    “Monday, January 22‹sup›nd‹/sup›, 2009 @ 12:34 pm” (using real angle brackets instead of Unicode single-angle-quotes, of course)

  • http://pulpjs.org Ken Snyder

    Some more from my personal list:

    1. For one project I found it very useful that MySQL 5 natively handles bitwise operators on 64-bit integers (BIGINT). I was disappointed to discover that SQL Server 2005 only allows bitwise operators on 32-bit integers. If I remember correctly, SQL Server 2005 silently returns bad results for such comparisons.

    I believe MySQL developed such support in order to handle ENUM and SET datatypes faster and with more flexibility.

    2. As was mentioned above, date manipulation and formatting functions are far superior in functionality and ease of use in MySQL compared to SQL Server.

  • Pingback: Ken Snyder

  • http://nikosbaxevanis.com Nikos Baxevanis

    For small to medium size applications (web applications, back-ends, etc) consider using an ORM like Hibernate or even an application framework like Spring (note: If you also work on the .NET space it’s NHibernate, Castle, EF). ORMs come with specific dialects for each DBMS and they will handle/optimize most of the things you mention above. Furthermore, your client doesn’t care about the implementation details you describe. He may easily request that you should switch from MySQL to MSSQL or even Oracle and then, you will have to rewrite all your data-access code.

    • http://leaverou.me Lea Verou

      Hey,

      Nice idea, I’ve never considered using an ORM. Why for small to medium? They don’t scale well?

  • http://nikosbaxevanis.com Nikos Baxevanis

    I don’t know if “scale” is what you actually need at this point. An ORM will map your object-oriented world with the relational. You still rely on your RDBMS for concurrency although Hibernate will optimize the queries sent in the database (and will also minimize round-trips if you choose to use an algorithm like HiLo for generating IDs). To build a responsive application you need to pay attention not only on your data-access layer but also on your service layer (SL). Your SL acts as a façade that your presentation layer uses to communicate with the rest of your system. So for small-medium size projects it’s OK to use Hibernate as is. In any other case you need additional stuff. A high performance full text search engine (Lucene), some 2nd level cache (Ehcache/Java, SharedCache/.NET), read/write batching to the database and even a NoSQL solution (CouchDB, MongoDB, Cassandra, Dynamo, etc).

  • http://dragonara.net/ dedicated server

    Thank you for letting me to know the difference on MySQL and MS SQL >this is really interesting.Thank you for sharing.

  • just so

    True for MySQL, CHEAP, nuttin else

  • Kumar Gaurav

    #5 can you provide some example. i guess its working on per row.