SQL Server – Advantages of Using SQL Server with Visual Studio

MySQLsql server

I have seen the forum dba.StackExchange, but this question involves Visual Studio as well, therefore I wanted to post this question here.

One of my old application was built on VS-2008 and MySQL back-end. After three years, I don't have any major issues with it and the database performance is also good. I am scaling this application to a WPF application and want to know whether SQL Server has any added advantages when used with VS-2010, other than that it is from Microsoft, over MySQL?

In most scenarios, I notice query execution in MySQL is faster as compared to SQL Server.

Best Answer

Advantages of SQL Server over MySQL:

  • Transactions. MySQL doesn't fully support transactions (any operation on MyISAM tables, as well as any DDL statements, will silently commit a pending transaction, which makes the transaction support practically useless)
  • SQL Server, Visual Studio, and the entire .NET ecosystem, are built to work together. While you can use MySQL with .NET, it doesn't integrate as nicely.
  • More powerful stored procedures. T-SQL has a complete set of imperative programming features, and while the syntax is still far behind a proper programming language, you can do amazing things with it.
  • You can import .NET DDLs into SQL Server and run .NET CLI functions from within T-SQL queries: this means any function you feel is missing from your SQL dialect can be provided through this mechanism (I used this once to order query results by geographic distance)
  • SQL Server has better replication support. If you ever need to scale your database beyond a single-server or simple master-slave configuration, MySQL will be trouble
  • Finer-grained locking. MySQL often locks entire tables; the result can be that if you run an expensive query joining two or three of your most important tables, these tables may be completely inaccessible for several seconds. SQL Server has more sophisticated per-row locking, which means even though you're querying some rows in a table, other rows can still be inserted or modified concurrently.
  • Native support for GUIDs as primary keys - if you prefer GUIDs for your keys, that is (which is one way to reduce migration pain).

Downsides:

  • Cost. MySQL is essentially free; SQL Server licenses come with a hefty price tag (unless you can use the Express version - but be prepared to pay once you hit the size limit)
  • Scriptability. MySQL is built with a command-line mindset, and it is very scriptable; SQL Server, coming from Microsoft, wants you to use the full-blown GUI for pretty much everything, including backup scheduling, migration, maintenance, etc.
  • Platform lock-in. MySQL runs on practically everything; SQL Server requires Windows.

Perceived advantages that don't actually hold:

  • Performance. Yes, MyISAM is faster than SQL Server, but it's also dangerous and incorrect. MyISAM doesn't support transactions, and it can't enforce foreign key constraints (due to its one-file-per-table approach). As a result, it is easy for a bug in your code (or simply a network outage) to corrupt your database. Most MySQL-based applications I have worked with include code that 'fixes' the database at regular intervals, trying to clean up the mess left behind by rogue queries and other glitches. Also, as soon as concurrency is involved, MyISAM's full table locking behavior easily devastates all performance you might have won.

That said, there are other alternatives - postgresql, which gives you many of the advantages of SQL Server, without the price tag and the vendor lock-in, is my personal favorite.

Related Topic