Is SQL Server "keeping up" with MySQL?
March 27th, 200827
Is SQL Server "keeping up" with MySQL?
March 27th, 200827

I read a comment in a Connect suggestion today that implied that implementing their suggestion would allow SQL Server to "keep up with MySql." Regardless of the actual suggestion, that made me laugh out loud… and since I was on the train, that got me some weird looks.

The actual suggestion was to add row constructors, e.g.:

INSERT dbo.Table (col1, col2) VALUES (1, 2), (3, 4), (5, 6), (7, 8);

Of course, SQL Server 2008 adds support for this construct. Better late than never. However, my impression is that this is the exception rather than the rule. When did MySQL finally add stored procedures? How about transaction support, SSIS-like capabilities, a scheduled job management system, and a relatively easy-to-use consolidated GUI management tool?

"LIMIT" notwithstanding, if SQL Server's functional specs weren't already light years beyond MySQL with SQL Server 2005, it will certainly leave them in the dust when SQL Server 2008 hits the streets.

Now, this isn't a slam on MySQL really, but rather I am questioning the narrow perception of many MySQL users. I am curious if you can provide a list of features (aside from "LIMIT") that MySQL has that SQL Server doesn't, or that MySQL had first. Perhaps my perception is the one that is narrow.

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

27 Responses

  1. Robert says:

    If you're crunching huge datasets, partitioned tables are a godsend. MySQL gives them to you for free. We use MySQL to crunch the big datasets, then we yank our summary tables into SQL Server (free version, limited to 4GB) and use it for the actual "live" database.

  2. David says:

    Most DB vendors are playing catchup with each other as each introduces new features. There are great features in Oracle, MySQL that I'd love to see in MSSQL (looks like 2008 has many of them).
    I was sitting in an Oracle Admin course a little over a year ago. The lecturer was preaching about how much better Oracle was (than MSSQL) He was using examples of great new features, being the only MSSQL DBA on the course I prefered not to start an Oracle vs MS argument, but had to quietly chuckle to myself as all his examples were functionality that had already been in MSSQL for years.
    Having read a little about SQL 2008 I think it's probably the closest MSSQL has come to matching all of Oracles "would be nice to have" features.
    Personally I think the few features in MySQL that MSSQL doesn't have hardly qualify it as better. We've managed without them for years and found alternative solutions. The cost on the box is hardly what makes a database expensive. The long term development and management costs are what makes the big difference. For me MS wins on this one!

  3. Matt says:

    I thought SQL Server 2005 finally brought an equivalent to MySql's LIMIT. Granted you have to use Common Table Expressions to get the results, but the use of CTEs comes with added benefits in the entire arena of ranking and various projections.
    I agree that MySql's original popularity was a direct result of its license. However, as a business, I really cannot use the product for free anymore. On the other hand, I think that despite this, it is still growing in popularity because it is still far cheaper to license MySql than SQL Server; and since stored procedures, sub-queries, and the like finally made it into MySql, it can actually  be considered for some decent data throughput.

  4. Shaun says:

    MySQL's only reason for popularity was marketing and it was free.

  5. Jogn Ingress says:

    100% of the mysql implementations I have seen were ALWAYS based on one or more of these points:
    1. free (with no limit in db size)
    2. Enterprise open source culture
    3. OS was not windows
    #3 is the biggest and is also the one I hear the most for choosing ORA. #1 is usually a false argument.

  6. Frans van Bree says:

    Personally I also like the free GUI tools MySQL Administrator and MySQL Query Browser. Query Browser creates a select-query when you double-click a table, allows you to update data in the result set via a grid, lets you jump to the last record, lets you search in the result set, has a pane with an overview of all supported functions and variables and jumps to the right section in the manual when you double click it (something I really miss in Query Analyzer or management studio; there is no "hard-link" to BOL).

  7. Daniel says:

    MySQL's biggest feature is of course that its free…
    They have spatial, replace (merge in MSSQL) en multiple inserts.
    We will have this soon…
    We have SSRS, SSIS, SSAS, Profiler, broker service and a great GUI.

  8. Steve Dassin says:

    >Some people get way, way, way too fixated on certain things and miss the big picture altogether.
    Yes indeed. Of course just what 'you' have in mind by 'the big picture' is an open question:)
    >database folks do tend to know only thier favorite product and hear only disinformation about the others.
    Yes indeed. Also an out for being intellectually and professionally lazy:)
    Also I would like to draw attention to the difference between the nah nanny fofo game of feature-tag versus real criticism. If your not aware of the later not to worry, like porn you will recognize it when you read it:)

  9. Saggi Neumann says:

    Hey Aaron,
    I think that I'd like to see shared-none clusters on SQL Server as high availability and load balancing solutions. IIRC, mysql, oracle and db2 have this feature.
    S. Neumann

  10. noeld says:

    Yes there are "many" ways to accomplish this: "query dependecies", "invalidating triggers" (your method), stored procedures with decaying policies …. etc
    Something "built-in" in which you can control the cache expiration on a "per-query" basis is extremelly useful in my current place.

  11. Rob Mills says:

    I don't know a lot about MySql but I appreciate your comparison.

  12. AaronBertrand says:

    See, I don't know if I agree completely with your comments, Geoff.  Several of the things Microsoft is adding seem to me to be quite unique, for example most of the enhancements to the BI side, and then things like dynamic management framework.  Other things, like MERGE, are not just about "keeping up with" some of the other platforms, but also about conforming more to the standard…

  13. Geoff says:

    I don't mean this in a disparaging way, but, from the outside, it looks like SQL Server tries to keep up with Oracle's features.
    Given that it is cheaper, that isn't a bad thing.

  14. AaronBertrand says:

    Yes, Frans, that seems like a good one.  (Though, I wonder if it's really all that much easier to implement than any of the numerous workarounds we have for that missing functionality?  Same question for row subqueries.)

  15. AaronBertrand says:

    noeld and Paul, I have implemented similar functionality that involves something as simple as a table with two columns: entity/table/view/etc. and last updated.  The app calls a stored procedure that checks the last updated value for whatever it is interested in.  If it is greater than the value the app has stored, it reloads the data, otherwise it just uses what it loaded last time.  The value, of course, is kept current with a very simple trigger.  While it is manual and not just a knob, it's quite simple to implement, all things considered.  In addition, it is very easy to tweak the trigger for your own purposes, e.g. for certain types of data it may not be important to refresh the cache when column A has changed, but column B and C absolutely need to be current.  Also, I would suspect that a simple built-in SQL Server implementation would invalidate the cache whenever an UPDATE statement fired, regardless of whether or not the relevant data actually changed.  Using my approach, I can write the trigger as I see fit and not have to worry about or learn little implementation details that someone else decided.

  16. AaronBertrand says:

    Whoa, let's keep it about MySQL.  If we want to produce a matrix of features in any of (Oracle, DB2, MySQL, Informix, SQL Server etc) but not in at least one of the others, we could be here for months.

  17. Scott says:

    Dunno about MySQL, but the Oracle flashback data archive was brought to my attention just yesterday:
    I'm not aware of a SQL Server equivalent.  I hope you guys can prove me wrong, because this looks really useful!

  18. Paul Nielsen says:

    Query Result Cache – hmmm, that sounds very intriguing. I suppose the result can be flagged as good until one of the underlying rows or ranges is modified. I want one.

  19. noeld says:

    "Query RESULT cache" this in my opinion is a *BIG* deal for "certain" apps MySQL has it  not MS SQL Server.

  20. Peter says:

    One more I've spotted is "row-subqueries"

  21. cinahcaM madA says:

    Peter: You can write your own RegEx function in three or four lines of C# code (depending on which RegEx feature you want to use) and expose it via SQLCLR.

  22. Peter says:

    It's got a Regular Expression function.
    Something similar might be useful occasionally.

  23. Paul Nielsen says:

    In an instructor's lounge a few years ago, a couple Oracle instructors were bashing SQL Server claiming that SQL Server was limited to foreign keys nesting limit of 32, and that it couldn't really handle database of more than a few Gbs.
    So yes, Adam, I agree with you, database folks do tend to know only thier favorite product and hear only disinformation about the others.
    When I do meet a DBA who is competent on multi-platforms (I worked with one last year who knew SQL Server, Oracle, DB2 and Informix) I'm impressed and envious.

  24. cinahcaM madA says:

    I think many DB people get tunnel vision… and of course many people who use non-MS platforms takes special joy in bashing MS.  Does MySQL have some features that make it better in certain cases than SQL Server?  Probably — I don't know the answer to that one, but based on its popularity there must be some compelling stuff there.  But of course, that doesn't mean that the entire product is better.  This reminds me of a debate I had with an Oracle guy some time ago, who told me that the main reason Oracle is better than SQL Server is its sequences feature.  He claimed to have designed an entire data modeling methodology around the idea of sequences.  Some people get way, way, way too fixated on certain things and miss the big picture altogether.

  25. AaronBertrand says:

    Hey, I was born on Feb. 30th, 1974.  🙂
    I was unaware of the natural join syntax.  After briefly skimming the doc, it seems like initially it was just shorthand to prevent naming each column twice in the ON portion… but now they are actually smart about it and conform to the standard by eliminating the redundant columns.  I am not sure if I would want that precise functionality in SQL Server, not only because I do not use SELECT * (so it would not be saving me any columns in my return list anyway), but also because it does not seem well-defined which "j" is returned.  I assume it would be the left-most table?  What if I had a right outer join?

  26. Denis Gobo says:

    I don't know if this is still the case but MySQL allows you the 'flexibility' to store invalid dates like 2002-04-31
    maybe we should add that to SQL Server 2010 so that we don't get errors and users don't complain when they fat-finger a date  😉
    that obviously was a stab at MySQL. It would be nice if SQL Server supported NATURAL JOIN, MYSQL does support it