Differences between MySQL and SQL Server

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

One thing you have to watch out for is the fairly severe differences in the way SQL nbsp 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 nbsp Server  T-SQL    SELECT TOP 3 WITH TIES   FROM person ORDER BY age ASC

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Differences between MySQL and SQL Server

Examples related to mysql

Examples related to sql-server

Examples related to tsql