MySQL or SQL Server: Look beyond politics and hype when deciding which to use

Takeaway: MySQL may be free, but what if money isn't the only determining factor? Find out how these two database heavyweights stack up against each other and how to decide which one to use as your database system.


Two of the most popular back-end data stores Web developers work with today are MySQL and SQL Server. They are fundamentally similar in that both are data storage and retrieval systems. You can use SQL to retrieve data with either because both claim support for ANSI-SQL. Both database systems support primary keys and key indices, so you can also create indices used simply to speed up queries and for constraining input. Further, both provide some form of XML support.

Aside from the obvious difference of price, what distinguishes these two products from each other, and how do you choose between them? Let's take a look at the core differences between these two products, including licensing costs, performance, and security.

Core principles are the root differences
The differences begin with principles: open vs. proprietary. SQL Server with its closed, proprietary storage engine is fundamentally different from MySQL’s extensible, open storage engine. You’re stuck with the SQL Server’s Sybase-derived engine for better or worse, while MySQL provides multiple choices such as MyISAM, Heap, InnoDB, and Berkeley DB.

This open vs. closed difference is, by itself, enough reason for some folks to choose one over the other. But, there are some technical differences as well. To begin with, MySQL doesn't fully support foreign keys, making it less of a relational database than SQL Server, which has full relational features. Also, MySQL has previously lacked support for stored procedures, and the default MyISAM engine doesn't support transactions.

Licensing costs: MySQL isn't always free, but it is cheap
When it comes to licensing costs, both products use a two-tiered scheme. With SQL Server, the most popular way to get a free developer license is to purchase a license for Microsoft Office Developer or Microsoft Visual Studio, both of which give you a free "development use only” SQL Server license. If, however, you want to use it in a commercial production environment, you'll have to pay for at least the SQL Server Standard Edition, which will set you back around $1,400 for five client connections.

On the other hand, MySQL is open source and licensed through the GNU General Public License (GPL). For developers, this means that as long as the project you are working on is also open source, you don't have to pay to use MySQL. If, however, you plan to sell your software as a closed-source product, you'll need to pick up a commercial license, which currently costs $440 for up to nine clients. Schools and nonprofits are exempt from this commercial licensing requirement.

Performance: Advantage MySQL
In terms of pure performance, MySQL is the leader, mostly due to its default table format, MyISAM. MyISAM databases are very compact on disk and place little demand on CPU cycles and memory. MySQL can run on Windows without complaint but performs better on UNIX and UNIX-like systems. You can experience additional performance gains by using MySQL on a 64-bit processor (e.g., one of those sweet SPARCstations), because MySQL uses an abundance of 64-bit integers internally. Much of the very busy Yahoo! Finance portal uses MySQL as a back-end database.

As I mentioned, with MySQL, you have a choice of table formats, but generally, these nondefault choices exact a cost in increased resource usage over MyISAM. Typically, though, these alternative table formats provide some additional functionality. For example, Berkeley DB supports transactions and actually has better performance with indexed fields than MyISAM.

When it comes to performance, SQL Server's strength—providing many more features than its competitors—is also its weakness. Granted, many of these features are geared toward performance tuning, but being a feature-rich environment means sacrificing something else. In this case, the cost is additional complexity, disk storage, memory requirements, and poorer performance. If you can't afford to support SQL Server with powerful hardware and trained expertise, you should definitely look elsewhere for a DBMS because you likely won’t be happy with the results.

It's worth noting that both systems will work well within either a .NET or J2EE architecture. Similarly, both will benefit from RAID, and both will perform best if the data store is on a hard drive or array that is solely dedicated to that purpose.

Replication and scalability: A dead heat
MySQL keeps a binary log of all SQL statements that change data. Because it’s binary, this log can be used to replicate data from the master to the storage on one or more slaves very quickly. Even if the server goes down, the binary record is still intact, and replication can take place. For query-heavy databases systems, MySQL scales easily into large data farms.

In SQL Server, you can also record every SQL statement, but doing so can be costly. I know of one development shop that had to do this because of other architectural issues, and the sheer volume of data that they were storing on tape was quite remarkable. Instead, SQL Server relies on elaborate mechanisms of record and transaction locking, cursor manipulation, and dynamic replication of data to keep database servers synchronized. If you're skilled at juggling these mechanisms, replication is pretty easy.

Security: Also tough to call
Both products have perfectly acceptable default security mechanisms, as long as you follow the manufacturer's directions and keep up with your security patches. Both products operate over well-known default IP ports, and, unfortunately, those ports draw intruders like flies to honey. My firewall logs are always chock-full of folks trying to contact nonexistent database instances on my machine over the default ports. Fortunately, both SQL Server and MySQL allow you to change that port should the default become too popular a destination for your taste.

Recovery: Advantage SQL Server
Failsafe and recovery is one area where MySQL, in its default MyISAM configuration, falls a little short. With MyISAM, a UPS is absolutely mandatory because MyISAM assumes uninterrupted operation. If it is shut down unexpectedly, the result could be corruption of the entire data store and loss of all your data. SQL Server, on the other hand, is far more resistant to data corruption. SQL Server’s data goes through multiple checkpoints as it passes from the keyboard to the hard disk and back out to the monitor. And SQL Server remembers where it was in the process even if it happens to be shut down without warning.

The best choice depends on the situation
If you were hoping to get an ironclad recommendation that one database is better than the other, I’m going to disappoint you. From my point of view, any database that helps you do your job is a good database; one that doesn’t is a bad database. I can tell you that to make a good decision about which of SQL Server and MySQL will help you most, you’ll need to look beyond politics and hype and instead look at function and mission. What do you want to accomplish?

If you're trying to build a .NET services architecture, synchronizing data between multiple disparate platforms, or learning the loftier precepts of database management, SQL Server will help you most. If you're building a third-party-hosted Web site, pushing a lot of data out to a lot of clients, or have a budget in the neighborhood of free, then MySQL will be your best bet.

--------------------------------------------------------------------------------
Which do you use, and why?
Is your Web app or site powered by MySQL or SQL Server? What led you to make this decision, and why was it the right choice for you? Tell us about it in the discussion below.

1. MySQL Performance fulsomepraise - 03/17/03
I am a little confused as to why you say that MySQL has the advantage in performance. How does this assertion relate to the database benchmarks listed by the Transaction Processing Performance Council (www.tpc.org)?

The TPC find SQL Server 2000 to comparable to Oracle 9i and, thus, at the top of scale in terms of database performance.
Reply 1.1. Is this relevant? uricon - 03/17/03
Yes, but if you look at the TPC members, MySQL AB do not feature, therefore MySQL stats aren't included. Not really a compelling argument!
Reply 1.1.1. Yes but why aren't they in there? Bunce - 03/17/03
From what I read in a recent article, TPC is basically a 'Pay-to-be-listed' type of rating.

Therefore if your latest benchmark is better than the rest, then you give your permission for the benchmark to be published (if you read the EULA of most RDBMS they prohibit benchmarks to be published).

In other words, mySQL either doesn't pay or give permission to be published because its not fast enough...

Check this out:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=38348

Cheers,
Andrew
Reply 1.1.1.1. Perhaps too expensive? irongut - 03/17/03
In my experience 'pay to be listed' ratings of this kind tend to be very expensive. Perhaps mySQL is not listed not because they feel their performance is lacking but because the product is essentially free or cheap and so doesn't have the financialmight of MS, Oracle, etc behind it.

I've always found mySQl to be efficient but I haven't run any reliable benchmarks.
Reply 1.1.1.2. Have you tried MySQL? KeeBored - 03/17/03
My dear friends, I have used both MYSQL and MSSQL to build database driven websites, and I have to say, MYSQL is remarkably faster than MSSQL. For example (for those of you who have not had the chance to try them both) a standard page that loads a data recordset of say 1,000 records will take around 4 seconds to load when using MSSQL while it takes 1 second to load in MYSQL. Not that is what I call PERFORMANCE.
I still use MSSQL for other applications though because of its failsafe/recovery features.
I bet Mr Bounce from Message #3 on this forum has probably not experienced it first hand.

Laters
Reply 1.1.1.2.1. Yes I have. 1) If your 1000 record set t... Bunce - 03/18/03
Yes I have.

1) If your 1000 record set takes 4 seconds to load then your coding is floored, not the database.

2) Read the article I posted you dill.
Reply 1.1.1.2.2. Nice code echava - 06/12/03
You are most likely having problems with the way your code is accessing the database because a 1000 rowset result takes much less than a second in SQL server. There is no way that in a DATABASE speed comparison you're going to find that MySQL is faster. You're testing the database through your web page. That's the problem. Four seconds to return 1000 rows? Nice try.
Reply 1.1.1.2.3. How not to compare MS SQL and MySQL D_Gilmour_1978 - 10/20/03
Measuring how long it takes for records to appear on a web page is not a fair test.
There are many factors that can effect this.
Eg. Apache vs IIS is the database on the same box as the web server etc.

I have used both products on large projects and have found MySQL to be lacking in features such as sub queries. This causes me to have to write application code to deal with this short comming. So for large complex applications I would have to say SQL Server is more suited to the job. However for web development i often choose MySQL as it is cheaper and does the job as good as anything as web applications tend to be less complex than n-tier 300 user applications (In my experience anyway).

I get sick of 'Microsoft Bashing' as SQL Server is a very good product. Compared to any other commercial product (DB2,Oracle etc)it is very good value for money.
Reply 1.1.1.2.3.1. PostgreSQL felipe.alfaro@... - 05/17/08
And people always forget of PostgreSQL, or Firebird. As if
MySQL was the only open-source damn-good RDBMS.
Reply 1.1.2. what about some performance benchmarks fulsomepraise - 03/18/03
It's not really an argument, just a question.

If MySQL is not included in TPC benchmarks for whatever reason then fine but at least the article should give us some other performance benchmarks instead of just asserting that MySQL beats SQL Serveron performance.
Reply 1.1.2.1. Qualitative Bucky Kaufman (MCSD) - 03/19/03
That's an excellent point - that comparisons should be backed up with actual metric values, but this piece was intended more as a qualitative analysis than a quantitative one... to help architects determine which tool to use for a job.

Quantitative performance metrics, especially between dissimilar systems, are often misleading. If I say that Product A supports 100 tics per timeslice, and Product B supports 100 times that - it would infer that Product B performs better. But if Product A requires 1000 events to perform the same function that Product B does in 1 event - it would flip the calculation upside down... because Product B can perform that one function faster, even though Product A performs functions in general faster. It all gets too weird.

That's why I focused on a qualitative analysis. I wanted to showcase the qualities of each. In other articles, where products that are more comparable are analyzed I do rely heavily on benchmarks to support quantitative claims.
Reply 1.1.2.2. Politics of benchmarks charlie.garry@... - 04/10/03
It is unlikely thatwe will see MySQL used in any TPC (e.g. TPC-C, TPC-H) benchmarks because these are controled by the commercial vendors (both hardware and database) who make the rules. You may want to check out eWeek which did a benchmark of all the major relational databases and included MySQL 4.0 (Server Databases Clash by Timothy ****, eWeek, February 25, 2002 http://www.eweek.com/article2/0,3959,293,00.asp)
Reply 1.2. Performance Metrics Bucky Kaufman (MCSD) - 03/18/03
There are a wide variety of benchmarks that you can use to compare MySQL and MS SQL, but "Transaction Performance" is not one of them. MySQL uses an extended ISAM format that is not transactional

My claim that MySQL has better performance than MS SQL is based on the fact that MySQL queries on large tables perform much faster than MS SQL queries on similar tables.

MS SQL is much more feature-rich and fault-tolerant, but those extended features come at the cost of performance.
Reply 1.2.1. Your mileage may vary JeriMorris - 03/24/03
There are so many factors that may affect performance that I find it hard to place much value in a blanket statement like "In terms of pure performance, MySQL is the leader."

Are you doing heavy-duty transaction processing? What about reporting and decision support requirements? Are you modifying existing data or inserting new data? Wide or narrow rows? Simple data types or images/blobs? What role does indexing play? How does performance change when the number of users changes?

Which is faster, MySQL or MS SQL? I have to think that the answer varies, depending on your needs.
Reply 1.3. TPC-C felipe.alfaro@... - 05/17/08
TPC-C is just propaganda. There is not way to compare two
different systems' TPC-C score as the hardware can be way
too different: one system can be a NUMA system using 2048
CPUs and the other one a federated database across a cluster
of 1,000 machines. Reply 2. Licensing uricon - 03/17/03
A couple of points about the licensing.
If you are using MySQL for development of even a commercial product, and then distribute your software WITHOUT MySQL server (or drivers / library code) then you do not seem to incur any license costs.
Also, educational users who wish to distribute the software must do so under the GPL, so there's no specific exclusion for them.
Clearly, if you are developing within an organization and wish to use the finished software with a copy of MySQL without making it GPL, a commercial license is applicable.
Many people choose to pay the commercial license fees so that they do not run the risk of making their software GPL by the implication of not buying a commercial license, and to have access to support and the latest functionality.

Details of licensing are included on the MySQL web site at http://www.mysql.com/products/licensing.html - it's very clearly explained and lacking the usual legalese that makes you roll your eyes and despair!
Reply 2.1. More about licensing petskippy@... - 03/25/03
Something the author of the original article missed.

With MySQL, the license is NOT per-client as he stated. Instead, it is PER-SERVER!.

Taken from the MySQL Licensing page:
"The MySQL Server license is per database server (single installed MySQL binary). There are no restrictions on the number of connections, number of CPUs, memory or disks to that one database server."

Therefore, whereas the author mentions that for $440 you get a single server with up to 9 clients. That is in error. The licensing is really such that your pay $440 per server if you are licensing 1 to 9 servers.

I really would appreciate if the Builder.com authors would spend more time researching their articles. I've seen articles with misleading informationseveral times. Reply 3. Political paragmehta - 03/17/03
Well this article is politics in itself. When the author talks about benefits of one product, without considering any benefits of other, then it's a political article in itself.

You say, performance of MySQL is better, have you done any tests yourself? Have you tested MySQL database on 200GB Database? Needless to say, this author has preference towards MySQL and his trying to force it to others.
Reply 3.1. apples and oranges Troy T. - 03/17/03
It's hard to compare MySQL and SQL Server performance when they are so different in features. Besides, sometimes real referential integrity, transaction logs, record level locking, are more important that pure speed.
Reply 3.1.1. SQL Server =Features and MySQL =speed wayland@... - 03/20/03
If you can develop easily within MySQL features then speed could be the difference between practical and impractical. If you have a spare bit of hardware and cash to run NT and SQL Server then this is probably the easiest road. However if the only hardware available is the Raq4 you rent at your ISP then MySQL is superb. Simply adding an NT server to your hosting fee would increase costs. Setting this up with the correct licences for SQL Server will really cost. The more money you spend the lessyou keep.
Reply 3.2. MYSQL Tendencies KeeBored - 03/17/03
Although I posted some other comments up there, I do agree with paragmehta's point. Evidently the author of this article has a tendency for MYSQL. There were many features mentioned on the article about MYSQL, but there was not much about MSSQL, although MSSQL is fully loaded with features.
Reply 3.2.1. Features felipe.alfaro@... - 05/17/08
How many of those features are you using?
Reply 3.3. Not that Political mattohare@... - 03/18/03
We all have our opinions. However, being a mostly-SQL Server developer/manager myself, I did not see his article as biased against SQL Server. In deed, he did point out places where SQL Server was the better choice.

To me, if the author had a bias, I just didn't see it in this article. Maybe that's because MySQL has never made it past preliminary evaluations with me.

I still hold that SQL Server is a better business management tool. It has full foreign key support. In my years of experience, that is _very_ important. Most of the problems I've had to resolve are around ghosts left in others' applications. ("A $100,000 loan is on my report, and nobody has one!")

I do see MySQL as being a fairly good datamart image of the maindatabase, for use to feed web pages. It could also be good for fast data collection.
Reply 3.4. MS Bashing Bucky Kaufman (MCSD) - 03/18/03
In reply to:
Needless to say, this author has preference towards MySQL and his trying to force it to others.
----- ----- ----- ----- ----- -----

Actually, the author (me) is an MCSD and a HUGE Microsoft fan. My preference is to use MS products regardless of the alternatives.

SQL Server is, in many ways, a superior product. But the simplicity of MySQL makes it the ideal choice for many distributed applications.

But more often than not, a '74 Ford Pickup is a better choice than a train for transporting freight.
Reply 3.5. Politics? felipe.alfaro@... - 05/17/08
Is not about politics. It's about freedom. Go the SQL Server
way and you will depend on Microsoft for the rest of your
life. This means you will be tied to a hardware platform
(PC), a software platform (Windows), and a single vendor
(Microsoft).

This is very, very bad. One day, you will want to migrate
to other system (hardware, software, vendor) and you will
be stuck, and you will regret, and the cost of migration
will be huge. And then you will realize why having truly
free, open-source systems (implementation and design) is
just what you business need to solve your business
problems.

The only caveat here is that some features you think you
need are only supported by one of those extremely
proprietary RDBMs. But people can live with BigTablle's
extremely simple semantics, so it should not be that
difficult to ignore those fancy, vendor-specific features of
SQL Server or DB2 or Oracle. Reply 4. Used for Mid-Large Systems? keHogan - 03/17/03
What is the best usage of each? For instance, I don't see critical systems using MySQL. Why would that be?
Reply 4.1. Critical Systems Brant Fitzsimmons - 03/17/03
In the article it stated that Yahoo was using it for their Yahoo! Finance portal.

Yahoo, having the 3rd most visited site on the Internet, with over 39 million unique users (according to Nielson//Netratings), qualifies as a very large, critical application. Wouldn't you agree?
Reply 4.1.1. Amazon mattohare@... - 03/18/03
Seems Amazon.com uses them too.
Reply 4.1.2. Yahoo Finance, not yahoo itself newm0004@... - 03/18/03
The article said that yahoo finance uses is, but your comment alludes to ALL of yahoo using mysql. I doubt that is the case.

Yahoo finance probably is not even in the top 100 sites.

Not bashing, just pointing out a minor flaw in logic.S
Reply 4.1.2.1. not really coryj - 01/05/04
Yahoo Finance is a massive site on its own. It's by far the largest finance on the web and I'd guess that it contributes a large portion of the overall Yahoo traffic.
Reply 4.1.2.2. Before you go pointing out flaws in logic... Brant Fitzsimmons - 11/15/04
...please re-read my post. I never said that Yahoo! was using it for their entire operation. In the very first sentence of my post I state, "In the article it stated that Yahoo was using it for their Yahoo! Finance portal."

Seems pretty clear.

Also, given that Yahoo is in the top 5, it's probably a good bet that their finance portal is hit really, really hard. If MySQL wasn't able to hold up to that kind of load it wouldn't have been chosen to do the job.
Reply 4.2. Really? felipe.alfaro@... - 05/17/08
I know of at least two of them. Google and Amazon. Google
has a huge (and when I say huge y mean really huge) MySQL
cluster. A cluster that is probably bigger than anything
you've seen before. Reply 5. Other Open Source Databases agingerich@... - 03/18/03
Why not include other open source databases like Firebird or PostgreSQL in your comparison. Both look like they're enterprise ready now.
Reply 5.1. Firebird? mattohare@... - 03/18/03
I evaluated PostgreSQL at one point. They were not ready for a windows platform, and I could not set up a Linux system at the time.

Is Firebird multi-platform? Reply 6. MySQL limited functionality m0ti - 03/18/03
MySQL suffers in terms of its power because it simply does not offer functionalities that even a moderate sized system are likely to use such as sub-queries (it's a pain to set up temp tables, fill -em up, join to 'em , and then tear them down).

Sure, it's fast, especially for very simple databases.

But MySQL doesn't come to the scale of SQL Server. It mops the floor with Access, but SQL Server's another story.

I would love to see a PostgreSQL vs. SQL Server comparison.
Reply 6.1. Good idea: Postgre v/s MS SQL privately_owed - 03/19/03
If anyone would be interested in writing an article comparing the virtues of Postgre with SQL Server, send me an e-mail: lamont.adams@cnet.com
Reply 6.2. MYSQL functionality and peformance JeriMorris - 03/24/03
mySQL doesn't enforce referential integrity constraints--or at least it didn't when I investigated it two months ago.

The mySQL documentation says that enforcing referential integrity in the database server isn't important, since the client software can do it. However, not all clients do it, so whether or not referential integrity enforcement (or any other feature) is important really depends on your own particular (present and future) needs.
Reply 6.3. Postgres vs MS SQL Industrial_Controller - 04/16/03
We did just such a comparison for a database migration project. PostgreSQL is a full SQL implementation and stacks up well against MS SQL in features. Cost made the critical difference in our choice. Postgres is free and that allows us to have aproduction database server as well as a testing/development server for only the cost of the hardware since we can run it on Linux, which is also free. Reply 7. MSDE option By_Jr - 03/20/03
in the article it states the way to get SQL, for developers but not comercial use, and for comercial use at retail price (not cheap), but the author forget about MSDE, wich is royalty-free to distribute with comercial applications, it does have restrictions (2GB database size with no restrictions on the number of DBs, 5 concurrent connections, and replication has some limitations, check out details at http://www.microsoft.com/sql/techinfo/development/2000/msde2000.asp )
With MSDE you can develop application for SQL and not being limited to sell them to mid/large bussines who can afford to buy SQL, and when they out grow MSDE, they can buy SQL standard. The benefits are for you and your clients. Reply 8. Forced to use SQL Server 2000 wayland@... - 03/20/03
Purchasing the software was a bit of an issue since you had o prove who the licenced users would be. As far as I can tell the 5 user licence issue is not implemented in software so I am not sure what happens when 10 users connect. The front end to this database is written in Access 97 for the supplied back end. There are a number of views implemented in SQL server which I believe are not available in MySQL. SQL Server looks like a much more capable RDMS but I feel MySQL would be fine for a stright MS Accss back end. I once used Oracle for simply storing MS Access tables, the result was terrible. A powerful Oracle server could not compete with a 486 based file server holding a 100MB data.mdb file. It's important to use the facilities of theserver if you expect a performance improvement over using an Access MDB file. Queries should be converted into views and stored procedures where possible. Anything not to drag the data over the network. I have read a review of PostgreSQL which looksa lot more comparable o SQL Server.
Reply 8.1. 5 connections means 5 concurrent connections as... D_Gilmour_1978 - 10/20/03
5 connections means 5 concurrent connections as far as i'm aware. I also believe that the requests are queued so it all just slows down.

MySQL does work ok as a backend to access however there are some very strange issues like having to have a timestamp in every table you want to be able to update. MSDE is a better option as it is intergrated very well (special project that uses it in MS Access 2000 and higher. I suspect the Jet engine will be a thing of the past very shortly
Reply 8.2. SQLitle felipe.alfaro@... - 05/17/08
SQLite is Microsoft's Access replacement. Calling MySQL an
Access replacement is just plain felony. Reply 9. Slanted Article wrohrbach@... - 03/20/03
The author purports to presenting an objective view of database choices and yet he presents only two choices. As far as I know there are many choices for a relational database other than MySQL and SQL Server. And to state that MySQL has greater performance when it does not even adhere to all of the properties of a true relational system is idiocy. Yeah, I'll use a database that cannot even support referential integrity and transactions for critical systems! The author seems to think referentialintegrity is just one of those nice-to-haves. I think I'll go with a database that at least offers the rudimentary features of a true relational database system. A better comparison would have been MySQL versus MS Access and MS Excel.
Reply 9.1. Scope Creep Bucky Kaufman (MCSD) - 03/21/03
I think I'm very objective, but with MS leanings. I compared MS SQL to MySQL because that's what the assignment called for. It's an important topic because for many development projects, those are the only choices you presented with.

About referential integrity - it's a wonderful feature, and when using InnoDB it is supported in MySQL, contrary to your statement. However, many projects are built and will continue to be built, in which data integrity is enforced in the application itself, rather than in the data store. Foreign Key support adds a level of overhead to the basic process of data storage and retrieval - which is the primary duty of a database server.

In Repy to, "A better comparison would have been MySQL versus MS Access and MS Excel." Those are data base clients. They bog down quite quickly when you try to use them as server apps.
Reply 9.1.1. The final nail in the coffin.. byrmol@... - 03/23/03
The second paragraph in your reply is absolutely disgraceful!

The only reason for a RDBMS is data integrity.

Do you honestly believe that the application code that "checks" that a row is valid for a given FK will be faster than what the DB engine could? You will issue 2 database statements (SELECT and then INSERT), while a RDBMS that has FK support issues just the INSERT.

I repeat.. absolutely disgraceful...
Reply 9.1.1.1. what the... stoecker@... - 03/23/03
some of you guys don't seem to have any clue about dbms and data storage and retrieval.

the best systems are _always_ using app logic to enforce integrity. if you're using the dbms it's simply extremly unprofessional and clearly tells you're never done big/critical jobs.
You can't just kill (for instance) a record because of a integrity rule! wohoooo!! hello! where did this come from? why was it saved/assigned? why the f* could this happen?

If you're just killing the $100k loan record this is great flub! extremly great flub!
Couln't it be, that some budget account was decreased by this value (even if you log it, even if you use thansactions, that won't help) - the questions is: WHY did this happen? What are the influences on _business level_ ?

Oh god - the're just killing the record... oh my...

//Marc
Reply 9.1.1.1.1. Opinions ESchlangen - 03/23/03
That's just your opinion. If you have a database that incorrectly deletes records due to referential integrity rules, you haven't set up your rules correctly. My experience with large database apps that try to control everything through the app coding is that they are slow and somewhere there is a bug in the code that either does what you've just described or leaves you with 500,000 orphaned records that do nothing except take up disk space. But that's just my opinion.
Reply 9.1.1.1.1.1. Not Just Space Taking mattohare@... - 03/24/03
Those records also tend to show up on reports, eventually. All you need is a select that does not link the records in an inner join to the parent table.
Reply 9.1.1.1.2. More garbage.... byrmol@... - 03/24/03
I can't believe I am hearing this from so called "professionals".

Your reply makes absoultely no-sense. You don't have any idea of what integrity means. You resort to some pathetically poor example with no context.

Obviously it is you who have never done any "big/critical" systems or you would know that these systems have many entry points of which some you don't control (ie. Many organisations intereacting with DBMS.. including competitors).

Your "best system" would last about 2 clock-cycles before irreversible damage was done.
Reply 9.1.1.1.3. Stick to the topic --peter - 03/24/03
I am sad to see such aggressive, yet uninformed opinions appear in what should a professional forum.

In my opinion, Troy stated it best on March 18 when he said: "It's hard to compare MySQL and SQL Server performance when they are so different in features."

This topic has gotten off on a big tangent. The point of the article was to determine which would be right for you. I do believe that most applications require the standard RDBMS features that MS SQL Server offers and MySQL does not,including transactions, foreign keys and referential integrity (henceforth RI) and stored procedure support. I have never designed, developed or consulted on a system that did not require most (if not all) of these standard features.

In other words, if you do not want any robustness to your data integrity, use MySQL. For web reporting, it is a great, inexpensive DBMS, but if you are developing a full-scale, enterprise level application, there is no comparison - you need to be using a real RDBMS such as MS SQL Server, Oracle, etc.... Yes these products cost money, but until a open-source DBMS supports these very necessary features, it cannot serve as the DB Server in mission critical applications.
Reply 9.1.1.1.3.1. TCO and ROI Bucky Kaufman (MCSD) - 05/18/03
In reply to:
I do believe that most applications require the standard RDBMS features that MS SQL Server offers and MySQL does not, including transactions, foreign keys and referential integrity (henceforth RI) and stored procedure support.
----- ----- ----- ----- -----

I have to agree - an app that doesn't use those optimizations is an app that probably doesn't do much anyway. But that $0 licensing fee on most MySQL installations shoots ROI through the roof, and TCO through the floor.I'm a Microsofty, but I LOVE building LAMP (Linux/Apache/MySQL/PHP) solutions, so Microsoft has been relegated to "big iron" status - useful mostly in large-scale solutions. With LAMP, a bare-bones PC and whatever development time I need, I can build a solid Intranet solution that the customer can just plug into their existing network and start using. Half of anything past the $500 for the PC that I can get out of the customer goes right back into my pocket (the other half goes to George Bush).
Reply 9.1.1.1.4. The truth... --peter - 03/24/03
As for things stated in this thread, I'd like to clear up a few issues starting with this one: Unlike MS Access, MSSQL does not, by default, do cascade deletions and updates (although I think stoecker believes this to be the case), nor would I recommend this without a lot of careful consideration.

Next, I think Bucky understated Data and Referential Integrity's (henceforth RI) importance. Data integrity should be done in the DB as much as possible because data integrity is part of the data.RI is one of the most important features of an RDBMS. Heck, the R in RDBMS stands for "Relational". Isn't RI helping maintain these relations?

Sure, if you want to provide a reporting database for the web, you could remove RI from that data store, although it doesn't gain you any performance since no data is being modified anyways. You also do not need transactional support for the web, so you could get a performance boost there.

Integrity belongs in the DB because rules that always apply to the data belong with the data. Not only is this better for performance, but safer for the data. What happens if your app developer forgets about the data integrity for a table or two. You have major problems that a good database design could have prevented. Business and app rules belong in the business layer and app, respectively. The app should never maintain data integrity.

I'm not saying that you cannot have a DB without data integrity. This surely can and will happen. I have seen databases that are collecting data that didn't require the data integrity. But most applications require data to be clean and relatable, and thus, the RDBMS should do this. To state otherwise is like stating that a book's Table of Contents and index donot have to match the actual contents of the book itself.
Reply 9.1.1.1.5. WHAT!?!? ccunningham@... - 03/26/03

You enforce referential integrity via constraints because there's no guarantee that all the code that accesses the data won't violate referential integrity rules.


Wow, I can't believe I read a statement like...well like your ENTIRE POST. I hope I never have to maintain an application that you've developed.

Most programmers I've stepped in after have had the same lackadaisical attitude toward database design and implementation, with crap results. Parent records without required children, child records without a parent, the list could go on, values outside of their domain... And the excuse was always "I'm doing it in code", but their code didn't handle all the situations that needed a constraint check, and then there's a big nasty mess of data that's found when someone finds crazy sh|t on a report, and then someone else has to go in there and figure out a way to clean everything up. It's pretty obvious you've never learned much about implementing and application using an RDBMS.
Reply 9.1.1.1.6. a note from a professional? julian@... - 04/11/03
i hate to be a flamer but what planet are you on?

a database that does not protect itself will corrupt. period.

ok, you're a single developer and you're handling all of the logic yourself in the middle tier, client code, whatever. so you're thinking you're safe?

are you perfect?

i certainly know that logic errors get into everybodys code. if you're not protecting at the rdbms you've suddenly killed you data store.

i have seen a lot of systems written by people with your opinion.they all suck. seriously. you should really get some experince with large production systems with medium to large teams that don't implement rdbms integrity checking. maybe that will help you formulate your opinions.
Reply 9.1.1.1.7. Case-by-Case Bucky Kaufman (MCSD) - 05/18/03
In Reply to:
"the best systems are _always_ using app logic to enforce integrity. if you're using the dbms it's simply extremly unprofessional and clearly tells you're never done big/critical jobs."
----- ----- ----- ----- -----

There's that word "always" again, that gets so many folks in trouble. Deciding where to put the business logic (app logic?) has many architectural condisderations - not the least of which is the resource availabilities of the end administrators. Then there's security - you might not want all of your logic in the same place as all of your data. Then there's the functionality - an Oracle web app might best be run on an sp-rich Oracle server, but a PHP app works best with MySQL - sans stored procedures.

... and that crack about the poster not having worked on large projects was off-topic, tacky and a pathetic attempt at bullying. Knock it off.
Reply 9.1.1.1.8. Where's the logic? dkehring - 01/05/04
You seem to have the terminology down but certainly not the concepts with regards to application levels. Any single application level is responsible for protecting itself based on its function. For example, the user interface layer is responsible for handling I/O interaction with the user. The business layer handles all logic about the "business" problem and the database layer handles data storage and data integrity issues. Your suggestion that the business layer is responsible for handling data integrity logic is just plain wrong. Yes, the business layer must perform some integrity checks _based on the relationships between business objects_ which are not necessarily the same as _relationships between database objects_. This is a classic problem with differences between object and relational mapping.

The database itself is responsible for protecting its data where referential integrity is an integral part. What happens if a completely separate application with different business rules wants to use the same database? Do we have to duplicate the database integrity logic in that application too so that application B does not corrupt data for application A?

Finally, the self-protection each layer must include is essential. Let's take a non-database approach. When one builds a business object, you add in rules to each property to check for valid values such as range or length, otherwise the data is rejected. Let's say a field can be only 10 characters long. Do you just put this kind of logic in the UI's text box control by setting the maximum length property of the text box? Of course not! The business layer must contain logic that protects the data it holds and maintains. What if the "user" of the business object is not a text box but another application? Adding such logic to the UI control just adds an extra level of protection but should certainly not be the definitive rule.
Reply 9.1.1.2. Never and Always Bucky Kaufman (MCSD) - 05/18/03
In reply to:
"The only reason for a RDBMS is data integrity."
----- ----- ----- ----- -----

Watch how you throw that word "only" around. using it like this makes you sound silly.
Reply 9.1.1.2.1. Yes ntcse@... - 09/30/03
Never use never.
Reply 9.1.2. MS-Access, Client or Server mattohare@... - 03/24/03
I wonder how many millions of dollars are wasted on the "less expensive" option of using Access/Jet instead of SQL Server. I know of a company here locally that had a wiz-kid Access programmer that took Access to a level way beyond design.

Nowthey spend thousands of dollars to maintain, not expand, their system.
Reply 9.1.2.1. Access on the Desktop Rules Bucky Kaufman (MCSD) - 05/18/03
In reply to:
I wonder how many millions of dollars are wasted on the "less expensive" option of using Access/Jet instead of SQL Server.
----- ----- ----- ----- -----

Access/Jet is fine for many things. The situation you mention is a problem of not having a long-term plan. Even the best software can't overcome that short-fall.

The most common situation I see Access used beneficially, and skillfully, in is clerical workers' desktop applications. They'll slap together an .mdb that works for them and use it for 10 years without changing hardly a thing.
Reply 9.1.3. I wish I had a dollar johnc@... - 09/23/03
"However, many projects are built and will continue to be built, in which data integrity is enforced in the application itself, rather than in the data store."

For every orphaned record I have fixed in a database with no Ref Integrity.. I would be a rich, rich man.. Reply 10. Tech support is also an important issue jmn - 03/21/03

The issue of technical support should also play a major factor in any major project. There can be little doubt that the community support provided by the Open Source community and the paid-for support provided by MySQL AB is equal to if not better than that which one can find for SQL Server by its vendors.

Furthermore, with access to the database source code a professional software developer can find out precise objective answers to questions rather than have to endure the spin-doctoring and evasion involved in the technical support provided by certain SQL Server vendors.

The cost of such support is a secondary issue, the quality of the support is far more important. This is the real strength of Open Source development tools.

Kind regards,

J.M. Nicholls
London
Reply 10.1. Open Sores Bucky Kaufman (MCSD) - 03/24/03
In re:
Furthermore, with access to the database source code a professional software developer can find out precise objective answers to questions rather than have to endure the spin-doctoring and evasion involved in the technical support provided by certain SQL Server vendors.
----- ----- ----- ----- ----- ----- ----- -----

Open Source is a double-edged sword. Although it fosters a community of experts, I find that Open Source developers are reluctant to certify or guarantee their own work with any teeth. They'll provide a tweak, but can't guarantee that it'll work with the 1000 or so other "tweaks" in the Open Source application. Also, if asked for documentation they'll often say it's "self-documenting".

These are problems that ANY software dev project can run into, but they are more pronounced in Open Source projects.
Reply 10.2. Amen felipe.alfaro@... - 05/17/08
Amen == ?Ol?! Reply 11. Pretty good article johnmeyer_1978@... - 03/24/03
Nice article about the relative merits of both. I have used both, and I can say that I go for mysql, mostly for cost and freedom reasons, but I do like some of the advanced tools of SQL Server. Reply 12. JOIN and get back to me jeffyjones@... - 03/24/03
Wow... look at all of the well documented research. [/sarcasm]

Throw in some complex joins, views and talk about stored procedures and triggers, then get back to me about which database has a performance advantage.
Reply 12.1. jeffy is right that there hasn't been a lot of ... kforslun@... - 04/02/03
jeffy is right that there hasn't been a lot of well documented testing and comparison.

I can refer you to an Infoworld article I've read:
http://www.infoworld.com/article/03/03/07/10osource_1.html

mysql's site has also linked to articles on performance comparisons.

In all the comparisons I've seen MySQL won over MSSQL.

That said, we need to clarify Jeffy's comment on seeing it tested with views, stored procedures and triggers. Quite simply, MySQL has none.

I've been developing with MySQL and PHP for 3 years. It works great. Everytime my stuff has been slow, it's been the way we did our code and how fast the CPU is. The database itself works great. Probably the biggest slow-down I've seen is that the longer the query string, the longer the query takes (I'm talking 3-4 screens full of auto-generated select statement here). The size of the data returned has almost no impact. It does not seem to take any longer to return 1 row, than a 1000. This of course is abstract, but with the tests we ran on our own system, it was always within 1 second. Reply 13. poor bad article, full of misconceptions glarriera@... - 09/20/03
I really found this article unaccurate and full of misconceptions about SQL Server. MySQL is no way comparable to SQL Server in performance, scalability and high availability. The paragraph talking about log transactions is full of errors. Note to the editor: please avoid this kind of poorly written articles.
Reply 13.1. Agree somewhat ntcse@... - 09/30/03
Looks like I'm not the only one that thought this article was slightly biased towards MySQL. I disagree with both the performance and the complexity assumptions.

SQL Server is AT LEAST comparable to MySQL as far as performance is concerned. I'm currently working with a 1GB database at work, and I have no complaint with SQL Server. I wouldn't worry too much even if MySQL was 10% or even 25% faster.

Funny, I picked up SQL Server pretty fast - I don't think it's overly complex. I mean you open Enterprise Manager, right click, New Table.... how simple do we need? I'd rather create tables and views using GUIs than taking the time to write the SQL out (which my friend needs to do with his MySQL databases on Unix servers). If you want to stick to pure ANSI SQL, go ahead and ignore the other advanced Transact SQL features.

I remember this author... he wrote another DB article a few months ago that spawned a whack of threads just like this one. See http://builder.com.com/5100-6388-1046266.html
Reply 13.1.1. Get a copy of MySQLFront as a MySQL GUI lauriek - 01/13/04
Suggest your friend downloads a copy of MySQLFront which is a nice windows front end which connects to any local or remote mysql database, its a lovely piece of work (and free!), like mysql itself...
Laurie
Reply 13.1.2. Performance Basis - Query or Update? Bucky Kaufman (MCSD) - 02/24/04
re:
SQL Server is AT LEAST comparable to MySQL as far as performance is concerned. I'm currently working with a 1GB database at work, and I have no complaint with SQL Server. I wouldn't worry too much even if MySQL was 10% or even 25% faster.
----- ----- ----- ----- -----

It's worth pointing out that MySQL performs much better on queries, but is LOUSY with updates. Furthermore, updating really large MySQL tables repeatedly over time is almost guaranteed to result in a corrupted table that needs to be rebuilt.

Whenever you say that "X" performs better than "Y" without specifying what performance metric is being used - you are just babbling.
Reply 13.1.3. 1GB? felipe.alfaro@... - 05/17/08
C'mon. I've used MySQL with 10TB+ databases and MySQL
does an amazing job. Not sure what the cost of running SQL
Server for such a database would be in comparison (only
licensing cost for all the 4-CPU machines is just huge). Reply 14. I'd like to prefer MySQL but I do prefer MS SQL o-mas@... - 04/09/04
Hi. I build muy applications with MS SQL beacuse it has more features, like sub-queries, triggers, transactions and stored procedures, and overall because it's easier to use. I know that the new version 4.1 of MySQL has sub-queries but the standard hosting don`t offer this version.

I think great part of the future of the software is in the open source so I will switch to any open database when it becomes possible to me. I'd like specially to know more about FireBird, wich is another open database