Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 9 down vote accepted

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.

share|improve this answer
    
What about InnoDB? How PostgreSQL integrates with VS? I tried a sample app, but pgsql's .NET connector has poor performance. Also PGSQL help is not easy if you stuck with VS and pgsql. –  RPK Jul 20 '11 at 9:48
    
InnoDB solves some of the issues, but not all; as soon as you mix MyISAM and InnoDB, you practically lose transactions and referential integrity. Other than that, InnoDB is the way to go if you have to use MySQL. –  tdammers Jul 20 '11 at 14:21
    
Postgresql probably integrates with VisualStudio as badly as MySQL does, but at least you get a properly functioning database. Anyway, if you're going to use ASP.NET, might as well go all the way. –  tdammers Jul 20 '11 at 14:22
    
+1 for the .NET dll imports and CLR integration! When done properly, this can make SQL Server extremely powerful/flexible! –  theChrisKent Jul 20 '11 at 15:07
    
I am using InnoDB and till date, no problems. MySQL's .NET Connector works well. It is well optimized for better performance. I do look forward to use PGSQL but unavailability of a mature .NET Connector, sets me back. –  RPK Jul 22 '11 at 19:04

One of the biggest advantage of using SQL Server with Visual Studio is that it is totally integrated with the IDE.

Another advantage is that it is the common choice in the community, and therefore you'll get more help and information if you encounter problems.

share|improve this answer
    
Help is easily available for SQL Server, MySQL and Oracle. Anything other than these, has less community. –  RPK Jul 20 '11 at 9:33
    
I work with both SQL Server & MySQL and found MySQL related help much less available. I assumed we were talking about .NET <=> DBMS related help, not simple DBMS usage or SQL syntax help –  user2567 Jul 20 '11 at 10:02

You can use LINQ with MS but not MYSQL

Language-Integrated Query (LINQ) is a set of features introduced in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents...

share|improve this answer
    
I guess LINQ is to used to query the collections? If it is also used to query the DB, how is it better than SQL? –  RPK Jul 20 '11 at 8:50
    
Its faster to write and they allow you to code at a higher abstraction level. If your using large datasets though i would stick with stored procedures because they are faster. –  Tom Squires Jul 20 '11 at 9:19
1  
LINQ is type-safe. If you use SQL queries, the compiler only knows that you're sending a string to the database, and you'll return a DataSet (which is about as dynamically-typed as it gets). Hence, if your query is wrong, you won't notice until you run it. With LINQ, the compiler knows internal details about your query, and it can check the types of the query parts and the result; if there's something in it that doesn't make sense, your code won't even compile. Which is a good thing. –  tdammers Jul 20 '11 at 9:38
3  
You can use Entity Framework to connect to a MySQL DB, which allows you to write LINQ to Entity statements against your MySQL database. –  Tr1stan Jul 20 '11 at 10:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.