MySQL vs MS SQL Server

Syntax Differences

Page 1 of 1

6 Replies - 7519 Views - Last Post: 11 October 2010 - 05:11 PM

#1 RandomlyKnighted   User is offline

  • D.I.C Lover
  • member icon

Reputation: 119
  • View blog
  • Posts: 1,384
  • Joined: 14-January 10

MySQL vs MS SQL Server

Posted 26 August 2010 - 12:44 PM

I'm working on a project using ASP.NET, C#, and SQL. I was told that I could install MS SQL Server Express on the computer where my application will be working; however, it only allows the database to go up to 4GB. I was also told that if I know that the database will be larger than 4GB then I need to use MySQL. They told me that to use MySQL I will need to change some of my code.

Here's my question: The code that they said I would need to change are they referring to the SQL code or are they referring to the code that makes the connection to the database?

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL vs MS SQL Server

#2 Atli   User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4241
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: MySQL vs MS SQL Server

Posted 26 August 2010 - 01:13 PM

MySQL and MSSQL are two very different things, so you will have to change both the SQL queries themselves and the database connection code.

.Net and MSSQL both being Microsoft products, they are made to work together. To use MySQL, however, you will likely have to install database drivers for ADO (or whatever you use to connect databases to your .Net code these days) or use special, MySQL specific connector classes. I can't really say what that is in detail as I have never used .Net in that way myself, but I'm guessing it won't be hard to figure out via Google.

Another thing to consider is that SQL Server is a commercial product, and as such costs money (and knowing Microsoft it will probably not be cheap). The Express editions are for students and hobbyists. If you plan on using it commercially you will have to buy the real version. MySQL, on the other hand, is completely free.

I'm not sure of the storage limits on SQL Server, but MySQL can -- theoretically at least -- store terabytes of data per database. The actual limits can be modified by your settings and OS file-system.

This post has been edited by Atli: 26 August 2010 - 01:15 PM

Was This Post Helpful? 0
  • +
  • -

#3 AdaHacker   User is offline

  • Resident Curmudgeon

Reputation: 463
  • View blog
  • Posts: 820
  • Joined: 17-June 08

Re: MySQL vs MS SQL Server

Posted 26 August 2010 - 03:53 PM

View PostAtli, on 26 August 2010 - 02:13 PM, said:

...so you will have to change both the SQL queries themselves and the database connection code.

You'll have to make some changes, but it really depends on your application. If you stick pretty close to ANSI SQL and don't use SQL Server-specific stuff, you probably won't have to change your queries too much. Likewise, if your connection code is using the interfaces and not taking hard dependencies on the SQL Server classes, it shouldn't be too bad. And even if you are taking the hard dependencies, it's still not that difficult, just a lot more work.

Quote

To use MySQL, however, you will likely have to install database drivers for ADO

I'll save him the Google - the MySQL ADO.NET connector is available here with docs here. The classes implement the same interfaces as most other ADO.NET providers, so it should be fairly familiar.

Quote

...as such costs money (and knowing Microsoft it will probably not be cheap).

Well..."cheap" is relative. Compared to, say, Oracle, SQL Server is fairly cheap. Compared to MySQL or Postgres...not so much. The actual price can range from a few thousand to tens of thousands of dollars, depending on what version you need.

Quote

I'm not sure of the storage limits on SQL Server, but MySQL can -- theoretically at least -- store terabytes of data per database.

The storage limitations of SQL Server Express are really just about licensing. A full version of SQL Server, according to Microsoft's comparison chart can theoretically store up to 524 petabytes. So you'll run out of disk space long before you hit any limit in SQL Server. In the retail versions, the limitations are on number of CPU cores and RAM available to SQL Server, not data stored.
Was This Post Helpful? 0
  • +
  • -

#4 RandomlyKnighted   User is offline

  • D.I.C Lover
  • member icon

Reputation: 119
  • View blog
  • Posts: 1,384
  • Joined: 14-January 10

Re: MySQL vs MS SQL Server

Posted 27 August 2010 - 02:45 PM

Even though the MS SQL Developer Edition is designed for development and testing can you use it for an application? I mean it comes with all the features most applications would need. To me it just makes more sense to pay the $100 for the developer edition and use it than to spend $5000 to get another version of the program.
Was This Post Helpful? 0
  • +
  • -

#5 AdaHacker   User is offline

  • Resident Curmudgeon

Reputation: 463
  • View blog
  • Posts: 820
  • Joined: 17-June 08

Re: MySQL vs MS SQL Server

Posted 27 August 2010 - 05:23 PM

View PostRandomlyKnighted, on 27 August 2010 - 03:45 PM, said:

Even though the MS SQL Developer Edition is designed for development and testing can you use it for an application?

No. That's the point. You can only use it to "to design, develop, test and demonstrate programs". The license specifically says that you may not use it in a production environment.
Was This Post Helpful? 0
  • +
  • -

#6 Bengie25   User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 53
  • Joined: 07-July 10

Re: MySQL vs MS SQL Server

Posted 11 October 2010 - 10:50 AM

http://www.microsoft...press/Database/

MS SQL 2k8 R2 Express supports up to 10GB db.

No, you may not use developer ed on any live/production DBs. The developer ed is just a full blow copy of MS SQL used to test stuff on. The same version for live DBs goes for $57,498 per CPU. I don't think MS would have an sympathy if they had to sue you over this as the price difference between $100 and $57,498 is enough to throw up flags.
Was This Post Helpful? 0
  • +
  • -

#7 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7505
  • View blog
  • Posts: 15,553
  • Joined: 16-October 07

Re: MySQL vs MS SQL Server

Posted 11 October 2010 - 05:11 PM

It depends on what objects are in your database. If you tables have standard datatypes and maybe just views, then pretty much any database will do. If you have stored procedures, you've locked yourself into the database. If you have an vendor specific toys, those are lock in. If you have a availed yourself of any myriad of database features; lock in.

If all you're doing against the database is SQL and the logic is all handled in your application, it can be pretty portable.

My C# code is always abstracted. I use DbConnect, DbCommand, DbDataAdapter, etc. If you put all your logic in the application, you can avoid database engine dependencies. Many commercial vendors do this, to service more customer architectures.

Using OleDb commands rather than SqlClient commands is also a good way to allow for multiple database support.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1