Differences between MySQL and SQL Server

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?

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

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.

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.

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

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.

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.

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.

@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.

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

> 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!

@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.

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.