149

I'm an ASP.NET developer who has used Microsoft SQL Server for all my database needs (both at work and for personal projects).

I am considering trying out the LAMP stack for some of my personal projects.

What are some of the main differences between MySQL and SQL Server? Is using stored procedures a common practice in MySQL?

Any advice or resources you'd recommend to help me with the switch?

To those who have experience with both, are there any missing features from MySQL?

DineshDB
  • 5,998
  • 7
  • 33
  • 49
sestocker
  • 3,522
  • 6
  • 27
  • 32

12 Answers12

143

One thing you have to watch out for is the fairly severe differences in the way SQL Server and MySQL implement the SQL syntax.

Here's a nice Comparison of Different SQL Implementations.

For example, take a look at the top-n section. In MySQL:

SELECT age
FROM person
ORDER BY age ASC
LIMIT 1 OFFSET 2

In SQL Server (T-SQL):

SELECT TOP 3 WITH TIES *
FROM person
ORDER BY age ASC
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jeff Atwood
  • 63,320
  • 48
  • 150
  • 153
  • 28
    and oracle is different too: select * from (select age from person order by ago asc) where rownum <= 3 Come back ANSI SQL, all is forgiven :) – gbjbaanb Sep 18 '08 at 14:47
  • 10
    Just ran across this. Reading about all the differences makes me cringe. The basics are the same (at least in terms of understanding), but the specifics are very, very different. – JasCav May 13 '10 at 18:21
  • 22
    maybe a dumb comment but why can't there be 1 sql language??...is there different html??...so why not 1 sql. – greaterKing Jul 13 '15 at 00:04
  • 6
    @greaterKing HTML is a set structure and standard by a committee/consortium, SQL.. isn't, it's just a scripting language and the only owner is the developer of the interpreter. It's kind of like Javascript in a way, every browser has their own JS engine and their own way of interpreting the script. It's only by sheer pressure from the community that efforts to bring that language to some kind of solidarity has been in place. But man do I wish they were all extremely similar with only minor differences – RedactedProfile Nov 30 '15 at 22:12
  • 1
    How does Sql Server == `T-SQL`? What does the T mean? – Kellen Stuart Sep 29 '16 at 17:22
  • 2
    T-SQL translates to Transact-SQL ;) Sql Server includes the language (T-SQL) but it is a software as a whole, with GUI, handling requests locally and through network etc. – Botond Bertalan Oct 19 '16 at 09:08
49

Lots of comments here sound more like religious arguments than real life statements. I've worked for years with both MySQL and MSSQL and both are good products. I would choose MySQL mainly based on the environment that you are working on. Most open source projects use MySQL, so if you go into that direction MySQL is your choice. If you develop something with .Net I would choose MSSQL, not because it's much better, but just cause that is what most people use. I'm actually currently on a Project that uses ASP.NET with MySQL and C#. It works perfectly fine.

Remy
  • 12,555
  • 14
  • 64
  • 104
  • 3
    Strictly speaking don't you think there is nothing like MS SQL. The thing that exists is "MS SQL Server". – Unbreakable Jul 05 '15 at 12:21
  • I am sure you know more than me. I am just a beginner can you please let me know if I am mistaken? Is there anything exact as MS SQL? – Unbreakable Jul 05 '15 at 12:25
  • 2
    I'm not sure what you are asking? – Remy Jul 08 '15 at 07:01
  • All I am saying is I don't think MS SQL is the right technical word to use. The RDBMS is called as MS SQL Server right? Not just the MS SQL. – Unbreakable Jul 08 '15 at 07:16
  • So when you talk about MS SQL, what you actually mean is MS SQL Server right? – Unbreakable Jul 08 '15 at 07:18
  • 3
    That is correct. Microsoft SQL Server 2012 or whatever version you use. – Remy Jul 08 '15 at 16:14
  • 5
    Programming is all about acronyms nowadays. Let's take the example of you talking about a `Lamp Stack`. A lot of people will probably just say LAMP or WAMP. Similarly, instead of saying Microsoft SQL Server we made it MS SQL Server to MS SQL to MSSQL – rassa45 Apr 24 '16 at 15:26
23

I can't believe that no one mentioned that MySQL doesn't support Common Table Expressions (CTE) / "with" statements. It's a pretty annoying difference.

ely
  • 74,674
  • 34
  • 147
  • 228
15

MySQL is more likely to have database corruption issues, and it doesn't fix them automatically when they happen. I've worked with MSSQL since version 6.5 and don't remember a database corruption issue taking the database offline. The few times I've worked with MySQL in a production environment, a database corruption issue took the entire database offline until we ran the magic "please fix my corrupted index" thing from the commandline.

MSSQL's transaction and journaling system, in my experience, handles just about anything - including a power cycle or hardware failure - without database corruption, and if something gets messed up it fixes it automatically.

This has been my experience, and I'd be happy to hear that this has been fixed or we were doing something wrong.

http://dev.mysql.com/doc/refman/6.0/en/corrupted-myisam-tables.html

http://www.google.com/search?q=site%3Abugs.mysql.com+index+corruption

Jon Galloway
  • 52,327
  • 25
  • 125
  • 193
  • 2
    myIsam is meant for really quick responses, it does not have forced foreign key check in reason to be quicker. Try using InnoDB engine if doing something more serious. I had some really nasty problems with MS Access, and can't forgive them for such irresponsibility, so I do not favorize any of MS's databases, let them do OSes, and give serious business to open source community... although you cannot be sure of anything since Oracle had overtaken MySQL.. – ante.sabo Sep 01 '10 at 08:11
  • 8
    Fair enough, but that's silly to ignore Microsoft databases based on Access. Access it is a desktop database and has nothing to do with SQL Server. You know StackOverflow runs on SQL Server, right? – Jon Galloway Sep 01 '10 at 22:59
  • @JonGalloway : Hi I am a beginner. I just want to know strictly speaking do we have anything as MS SQL. I think what exists is MS SQL Server. Can you please clarify me on this. It might be a common word so you might have used it as MS SQL and it would have made sense to every one. But I am a beginner so I am kind of fixated with terminologies to get the concept. Please let me know what you are talking about is MS SQL or MS SQL Server? – Unbreakable Jul 05 '15 at 12:27
  • 1
    When people say MSSQL, TSQL they generally mean the SQL or Transaction SQL (TSQL) language shipped with the product Microsoft SQL Server. This should not be that difficult to find out. Read on https://en.wikipedia.org/wiki/Microsoft_SQL_Server – Faiz Oct 03 '15 at 17:42
  • @JonGalloway and here is the reference: https://stackoverflow.blog/2008/09/what-was-stack-overflow-built-with/ – Moayad Hani Abu Rmilah Feb 22 '17 at 08:35
8

Everything in MySQL seems to be done closer to the metal than in MSSQL, And the documentation treats it that way. Especially for optimization, you'll need to understand how indexes, system configuration, and the optimizer interact under various circumstances.

The "optimizer" is more a parser. In MSSQL your query plan is often a surprise (usually good, sometimes not). In MySQL, it pretty much does what you asked it to do, the way you expected it to. Which means you yourself need to have a deep understanding of the various ways it might be done.

Not built around a good TRANSACTION model (default MyISAM engine).

File-system setup is your problem.

All the database configuration is your problem - especially various cache sizes.

Sometimes it seems best to think of it as an ad-hoc, glorified isam. Codd and Date don't carry much weight here. They would say it with no embarrassment.

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • 4
    Modern MySQL defaults to Innodb though, just a point of clarification. I think after 5.1 or so. Innodb does support transactions, foreign keys, and row level locking as opposed to table level locking with MyISAM. – Joseph Hamilton Jun 19 '15 at 17:47
7

Frankly, I can't find a single reason to use MySQL rather than MSSQL. The issue before used to be cost but SQL Server 2005 Express is free and there are lots of web hosting companies which offer full hosting with sql server for less than $5.00 a month.

MSSQL is easier to use and has many features which do not exist in MySQL.

Abdu
  • 16,129
  • 13
  • 59
  • 84
  • 2
    I think price is the only real reason to choose MySQL over MSSQL. Sure, there is some cheap MSSQL hosting but it is rare and often severely limited on storage. – Craig Nov 23 '08 at 20:28
  • sql server storage is like 100M-500M. For a regular website this seems plenty. – Abdu Apr 20 '09 at 00:22
  • 21
    I would say that the licensing is a pretty good reason to go with MySQL. – Dr. Mike Sep 02 '13 at 12:11
  • So far my issues are that are mySQL does not support table based variable types and also you cannot reopen temp tables. Pretty much show stoppers for me. – realtek Apr 08 '14 at 21:39
  • 3
    Group_concat and REGEXP are more than enough reasons to use MySQL over SQL Server (even if the two licences were switched) – Michele Feb 24 '15 at 11:51
  • @Michele SQL Server supports Regex with CLR integration. Don't know what Group_concat. I am sure there's an equivalent way to get the same results. With CLR integration you have the .NET Framework at hand. As for licenses, you can use the free Express edition up to 10G database size. Most people need less. – Tony_Henrich Feb 24 '15 at 17:23
  • @Tony_Henrich Oh yes, of course. I wrote two CLR in C# myself to do those two tasks (group_concat aggregates multiple rows in one string). The point is such functionality, regex in particular, are essential tools for data analytics and should be included by default. – Michele Feb 24 '15 at 22:08
  • 1
    another reason why I would go with MySQL would be that if you want a local database and you go for MS SQL Server you would have to have a windows machine running, which includes more licensing costs... specially if you want to keep it all up to date – Pedro Braz Jul 15 '15 at 20:24
  • 2
    Downvoted because I think it's biased and obviously inaccurate to say there isn't a single reason to go with MySQL over MSSQL. SQL Server Express has severe limitations that MySQL does not, and no serious project is going to use Express, meaning the cost issue is still very valid. Most of the biggest companies out there chose MySQL/MariaDB over MSSQL. Personally, I would too, not just for the licensing and cost issue but because in my experience MySQL is just a lot easier to work with. MySQL tends to just work, while I find myself fighting with MSSQL occasionally to get it to do what I want. – dallin Aug 24 '16 at 17:13
  • doesn't ms sql server take up a ton of hard drive space whereas mysql is much smaller.. Also mysql is typically installed as part of a WAMP package. – barlop Oct 20 '16 at 17:33
  • @barlop In this era of cheap terabyte drives, disk space is a none issue. SQL Server takes less than .5 gig which is fraction of a tera byte drive and you get alot more functionality than MySQL. Yeah it uses more than MySQL in terms disk space, but so what? Who cares about WAMP and why it's relevant here. WAMP means installing other stuff along of MySQL. Either My SQLServer or MySQL is non issue. Just double click on the installer and some OK buttons. – Tony_Henrich Dec 04 '16 at 04:23
  • @Tony_Henrich not cheap if SSD, and SSD may be wanted for eg quietness. – barlop Dec 04 '16 at 12:46
  • 250GB SSD's are less than $100. The days where you worry too much about disk space HD or SSD are over. 0.5GB is a ton of disk space like 10 years ago. Welcome to 2016. – Tony_Henrich Dec 05 '16 at 07:20
6

I think one of the major things to watch out for is that versions prior to MySQL 5.0 did not have views, triggers, and stored procedures.

More of this is explained in the MySQL 5.0 Download page.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jon Limjap
  • 94,284
  • 15
  • 101
  • 152
5

Both are DBMS's Product Sql server is an commercial application while MySql is an opensouces application.Both the product include similar feature,however sql server should be used for an enterprise solution ,while mysql might suit a smaller implementation.if you need feature like recovery,replication,granalar security and significant,you need sql server

MySql takes up less spaces on disk, and uses less memory and cpu than does sql server

prince
  • 51
  • 1
  • 1
4

@abdu

The main thing I've found that MySQL has over MSSQL is timezone support - the ability to nicely change between timezones, respecting daylight savings is fantastic.

Compare this:

mysql> SELECT CONVERT_TZ('2008-04-01 12:00:00', 'UTC', 'America/Los_Angeles');
+-----------------------------------------------------------------+
| CONVERT_TZ('2008-04-01 12:00:00', 'UTC', 'America/Los_Angeles') |
+-----------------------------------------------------------------+
| 2008-04-01 05:00:00                                             |
+-----------------------------------------------------------------+

to the contortions involved at this answer.

As for the 'easier to use' comment, I would say that the point is that they are different, and if you know one, there will be an overhead in learning the other.

Community
  • 1
  • 1
Cebjyre
  • 6,552
  • 3
  • 32
  • 57
  • Also, correct ansi support for group by, automatic timestamp columns, MUCH simpler paging... – Joel Coehoorn Oct 02 '08 at 14:49
  • 1
    MySQL's timezone support is significantly broken, I don't think that's a useful feature. Do it in the application instead. – MarkR Nov 23 '08 at 20:38
  • I haven't had any problems with timezone support, could you go into some more details or provide a link please? – Cebjyre Dec 16 '08 at 04:13
  • 2
    Why would you need timezone support in the database? Use UTC everywhere and render in the front end/gui using the OS's local timezone. – oxygen Mar 14 '17 at 18:11
3

Anyone have any good experience with a "port" of a database from SQL Server to MySQL?

This should be fairly painful! I switched versions of MySQL from 4.x to 5.x and various statements wouldn't work anymore as they used to. The query analyzer was "improved" so statements which previously were tuned for performance would not work anymore as expected.

The lesson learned from working with a 500GB MySQL database: It's a subtle topic and anything else but trivial!

dlinsin
  • 19,249
  • 13
  • 42
  • 53
3

@Cebjyre. The IDE whether Enterprise Manager or Management Studio is better than anything I have seen so far for MySQL. I say 'easier to use' because I can do many things in MSSQL where MySQL has no counterparts. In MySQL I have no idea how to tune the queries by simply looking at the query plan or looking at the statistics. The index tuning wizard in MSSQL takes most of the guess work on what indexes are missing or misplaced.

One shortcoming of MySQL is there's no max size for a database. The database would just increase in size till it fills up the disk. Imagine if this disk is sharing databases with other users and suddenly all of their queries are failing because their databases can't grow. I have reported this issue to MySQL long time ago. I don't think it's fixed yet.

Abdu
  • 16,129
  • 13
  • 59
  • 84
  • 2
    On the other hand, I've been annoyed by the management studio console not respecting undo properly (it's possible to have a valid query that it chokes on, because it doesn't realise the text has been updated), and the lack of tab-expansion (compared to mysql shell). There are minuses on both sides. – Cebjyre Dec 16 '08 at 04:17
1

Spending some time working with MySQL from the MSSQL to MySQL syntax POV I kept finding myself limited in what I could do.

There are bizzare limits on updating a table while refrencing the same table during an update.

Additionally UPDATE FROM does not work and last time I checked they don't support the Oracle MERGE INTO syntax either. This was a show stopper for me and I stopped thinking I would get anywhere with MySQL after that.