MySQL, Oracle, and SQL Server, differences?

Factual Questions
1

IT people, I need your help. I am in school for Information technology and am in my first database class this semester. Our teacher has stated that MySQL is for smaller scale type of databases with a lower number of users, and Oracle is for larger scale, many user scenarios. That, I’m afraid is all I know.

A kid in another one of my classes has done some programming, but is a Microsoft kind of guy. During a group projects (Systems Analysis class), when the topic of databases came up, I brought up that we should select one. His response: They are all the same.

I argued, and said from what I know, MySQL doesn’t have many features that Oracle has, and so forth. He then goes on to say Microsoft SQL Server is just as good as Oracle.

I was just wondering if there were comprehensive lists detailing the differences between these three database types. A list of features and if they have it or not. I did a google search but cannot find anything comprehensive, just a bunch of small posts on various message boards with one or two differences.

Anybody have the straightdope on this? I need to prove to this kid that my teachers know more than him. He seems to think they are wrong.

2

It’s really a matter of preference. I prefer Oracle, but that’s probably because the majority of my databases are on Oracle platforms. I have two SQL Server databases and I really don’t like them, but that’s probably because it becomes much more difficult to transfer data between them when they’re on different platforms.

3

MySQL is not an enterprise-capacity DBMS. MS SQL Server and Oracle are. They’re different in some details (and have different SQL syntax for some of the non-standard things they both do), but are overall very similar in capabilities, and it’s primarily a matter of personal preference which one you’d use for a particular purpose.

4

Traditionally, MySQL lacked stored procedures, triggers, transactions, replication, and other features of the major enterprise players. But in recent MySQL versions most, if not all, of these features have been implemented. So, MySQL is closing the gap, but Microsoft is firing back by offering a “light” version of SQL Server 2005 called Express that is free, including for commercial use.

It has been years since I’ve worked with Oracle, but the T-SQL vs. PL/SQL stored procedure languages are vastly different.

5

Yeah. The last time I tried to do much with PL/SQL I ended up giving up in mild disgust going, ‘how the heck do Oracle people manage without running stored procedures that end up returning multiple-row query results to their application layer software?’

Myself, I’m quite comfortable lately in the microsoft camp.

6

Another important difference between SQL Server and Oracle is that SQL Server runs only under Windows, so you’re limited on what hardware/OS you can deploy to. Oracle (the core database, anyway) runs on just about anything, from a desktop PC to huge enterprise class SMP monsters.

7

Define “enterprise-capacity.” MySQL runs some of the largest, most complex datasets in the world. Oracle does a lot more of them, though, but then nothing really compares with Oracle.

8

Unfortunately, the answer is “that depends”

Your teacher is wrong to say that MySQL is only for smaller databases with low user numbers. Wikipedia would be a good example of a MySQL based system serving large volumes of data to huge numbers of users.

Where MySQL has traditionally been week is for large, critical business applications. Historically, this was due to limited functionality and a failure to comply with ACID standards for ensuring data integrity. To a great extent these weaknesses have been addressed, and I’d personally consider MySQL Enterprise Server 5.0 for evaluation for pretty much any project where I’d consider Oracle or SQL Server.

Whether or not I’d pick it is another issue. A lot of this is down to fear and prejudice - my company knows Oracle, SQL Server and DB2 well, and I’m not going to raise any eyebrows picking these for a key project. Now technically I’d be surprised if MySQL couldn’t be used for pretty much any of my companies products (our databases are fairly standard, relatively large with high usage but nothing too unusual in terms of requirements for functionality) but I know that some of our customers would query its use, or view it as a low budget, unproven platform.

So technically, I would argue that MySQL would be suitable for a majority of large business applications. Politically, that’s a different question.

9

Me too.

For a long time I thought SQL Server and Oracle were roughly equivalent, before I actually had to do real work with Oracle. My God. What a trainwreck. I honestly don’t see how Oracle as a company will stay in business if they don’t get their act together and figure out that most people nowadays don’t want to go to weeks and weeks of training just to do basic DB management and run a few queries. Give me SQL Server any day of the week; it’s designed for people who actually have to do work, not wallow in the details of DB maintenance and methodologies.

10

Disclaimer - I am an Oracle DBA so therefore much more familiar with Oracle than Microsoft SQL Server. The information I have is relayed to me (some of it verified by myself) by Microsoft SQL server colleagues. I am comparing Oracle 12.2 and Microsoft SQL Server 2005.

One of the biggest advantages of Oracle is the variety of hardware platforms and operating systems supported: many flavours of UNIX, Linux, VMS, Macintosh OS X, etc. With Microsoft SQL Server you are stuck with one platform.

Installation and configuraion of Microsoft SQL Server is much easier. No contest there. Oracle has made great strides in this area with Oracle 10 but still haven’t reached Microsoft SQL Server’s ease of use.

In Oracle, it is much easier to add another server for load balancing (Oracle RAC) than it is to add a new system to a cluster with Microsoft SQL server - I hear that with Microsoft SQL server you need to create additional tables and rebuild some objects.

Oracle performs better when you enable “row-level” locking in the database. SQL Server 2000 would escalate locks frequently, Oracle would not; and SQL Server 2005 pays a performance price for trying to implement the row-level locking that is a standard feature of Oracle.

Oracle has more indexing options: besides “b-tree” indexes there are bitmap indexes and cluster indexes.

Oracle logminer allows you to view all the history of changes to your database from the transaction logs.

Oracle provides flashback query which allows you to view a table at a point in time in the past (SQL server has this ability only within the context of a transaction)

Oracle 10G has a “recycle bin” feature where drop objects remain inside the database in a “recycle bin” until the recyle bin is emptied (similar to your desktop computer’s trashcan)

When you audit SQL statements in Oracle you can also audit the bind variables used in queries.

Database procedures (PL/SQL) can be “compiled” for better performance

SQL Server 2005 has shown less security flaws than Oracle 10.1 - one thing Microsoft has done right.

In the business world, Oracle still is used for the larger databases (example: one of our customers here had a single table that was 3 or 4 terabytes in size - with huge amounts of transactions per hour.)

11

It is possible with PL/SQL to run stored procedures that run multiple-row query results.

12

Oracle is also feeling the pressure, of course. Here is their free database:

13

Thanks for the replies so far, great info. So basically it is due to preference, unless you need something for a specific task? Something you want to focus on that one DBMS performs better?

So I am potentially wrong, depending on the versions of MySQL, et al? All DBs are (now) created equal?

14

I wouldn’t go that far. The popular database products have a number of significant differences, but most of them don’t come into play until you start designing large, highly complex systems. Wikipedia has a pretty extensively detailed comparison of relational database management systems.

15

No, not all databases are created equal. For a thousand concurrent sessions all updating tables at the same time, you will find that MySQL just does not scale well for the task. SQL Server vs. Oracle is a closer comparison, but you would definitely not want to use a database like MySQL for a database the size of, let’s say, Amazon’s online store, or the billing system for a large phone company like Verizon.

16

I used to maintain a MySQL cluster system that regularly peaked at over 900 queries, not including intra-cluster replication. It was admittedly not an update-heavy application, though.

Given the choice, I probably would have still used Oracle for the project.

17

For some real-world advise, choose Oracle as your training tool. Anyone that wants a career in the business oriented area of IT needs it. It the hard skill that is worth the most $$$$ out of almost anything related in my experience. There are lots of different jobs that require Oracle experience (and database experience in general). If you are going to pick one, you might as pick the one that will serve you best as a practical skill.

18

That is what I would expect from a “kid who has done some programming.” I am not qualified to rattle off the differences (excellent entries above do that) but just because you can write SQL for all of them doesn’t mean they’re the same. As you have seen by now there are some significant differences.

Aside from pure technical considerations, there are things like how well does the DBMS play with what else is in your architecture, how well the product is supported, cost, related products offered by the company, and other more business-decision type stuff. Oracle offers lots of ERP functionality as well as their DBMS, trying to set up the same kind of relationship that happens when you buy Windows and MS Office. You don’t have to get them both, but the theory is that it all works nicely together (guess what–it probably doesn’t).

Doesn’t mean your teacher is right, though.

19

SQL Server DBA chiming in here. From my experience with MySQL, which is somewhat outdated, it was definitely not anything approaching an enterprise-level DB tool. It was a glorified file dump for storing data, and god forbid if you wanted your DBMS to perform typical DBMS functions (like say, adding or comparing dates) correctly. It all had to be handled by the front-end code.

Being free, of course, it was the tool of choice for many many people who had no real business designing databases :slight_smile:

Oracle was (and still is in many respects) a clunky powerhouse of a DBMS. It’s not an easy piece of software to leanr by any means, and because it’s so tedious, the DBAs tend to be cranky and anal. (this is all good-natured ribbing of the Oracle guys). It is in use at a lot more places than SQL Server because a lot of people got the impression that SQL Server wasn’t up to Enterprise level tasks. This was not true, but many of the people making decisions used logic to the effect of “what if we’re unexpectedly hugely successful beyond our wildest dreams and need to scale out our database by 10,000% and get tons of new functionality? Then SQL Server won’t work!” Well, maybe not, but you’re going to be redesigning the architecture anyhow, so going with the cheaper and easier to manage option would have been wiser.

SQL Server will do 95% of anything that Oracle will do, but those differences can be critical, especially if you don’t know what they are. In the end, it’s typically not database functionality that’s lacking, but something like XML support (which I beleive Oracle adopted before Microsoft). And it is way easier to manage (though Microsoft seems to be making Oracle’s job of catching up easier with each new release of the DB Management UI). One of the downsides of this, however (as with MySQL) is that because it’s cheaper and easier to manage, you get people who really should not be managing databases thinking that they are DBAs, and creating a whole slew of problems that cause the rest of us frustration.

20

Hmm… could you post an example? Is it something that’s new??

And does it depend on what library you’re talking to oracle with and what your application language is written with??
I remember googling and researching, and hearing that what I was wanting was something that was absolutely impossible with PL/SQL, and that the people who worked with it seemed to think wasn’t anything important.