Difference between MySQL and SQL Server

Both MySQL and SQL Server do a good job of supporting all major protocols for accessing their respective databases. A standard protocol is the “programming language” used for a program to communicate to a SQL database. The most common protocol is called tSQL, or Transact SQL. Transact SQL is a series of statements that a program can use to access data and create new tables in a SQL database. The statements can INSERT new records, DELETE old records and UPDATE existing records, along with a myriad of other functions.

The international standard for this programming language is referred to as ANSI SQL. ANSI stands for American National Standards Institute. ANSI is a collective governing body that determines what the standard language will be. For example, ANSI decides such things as the word INSERT will be used in the programming language as apposed to the work ADD to insert a new record into a database.

While both databases do a good job of supporting all major protocols, one of the most frequent complaints about MySQL is that it does not completely follow the ANSI SQL standard. This would not necessarily be any issue if your need for data storage never outgrows the capability of MySQL. However, if you data is constantly growing, there may be an eventual need to upgrade from MySQL to SQL Server to handle the vast amounts of data. In a situation like this, any application that has been written to manipulate a MySQL database will have to be revisited to be sure that the program is able to “communicate” with the new data store.

To give a better idea of the differences in MySQL and SQL Server, MySQL is geared more toward selecting data so is can be displayed, updated and saved again. MySQL is weaker in the areas of inserting and deleting  data. MySQL is an excellent choice, for other reasons that we will discuss later on, for data storage and referencing data. MySQL is not the best choice however, for creating, inserting and deleting data from tables on the fly.

For an Internet application, MySQL would be a good choice for tracking clients and creating dynamically populated pages with information from the database. For an application of moderate to large scale that is used for commerce of any kind, SQL Server is by far the better choice with more options and functionality to add, manipulate, update and delete data. Following are some specific technical differences in MySQL and SQL Server when it comes down to the ANSI SQL standard:

• • MySQL does not support Triggers, SQL Server does.
  • MySQL does not support User Defined Functions, SQL Server does.
  • MySQL does not have Cursor Support, SQL Server does.
  • MySQL does not support Stored Procedures, SQL Server does.
  • MySQL does not have FULL JOIN capabilities, SQL Server does.
  • MySQL has limited Import/Export capabilities, SQL Server has full support for Import/Export.
  • MySQL has limited Transaction Support while SQL Server offers full Transaction Support.

So, it becomes clear that SQL Server is an obvious choice for the company or individual that needs to have FULL control over their data to manipulate and change it as they need.

Via http://www.tometasoftware.com/mysql_vs_sqlserver.asp

and here are the syntaxes which are difference between SQL and MySQL.