Sunday, May 18. 2008
Recommended Books: PostgreSQL 8.4 Official The SQL Language MySQL Administrator's Bible SQL Server MVP Deep Dives
Comparison of Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3
The below is by no means an exhaustive comparison of these 3 databases and functionality may not be necessarily ordered in order of importance. These are just our experiences with using these 3 databases. These are the databases we use most often. If we left your favorite database out - please don't take offense. Firebird for one has some neat features such as its small footprint and extensive SQL support, but we have not explored that Db.
People ask us time and time again what's the difference why should you care which database you use. We will try to be very fair in our comparison. We will show equally how PostgreSQL sucks compared to the others. These are the items we most care about or think others most care about. There are numerous other differences if you get deep into the trenches of each.
For those looking to compare MySQL and PostgreSQL you may want to also check out http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
If you really want to get into the guts of a relational database and the various parts that make it up and how the various databases differentiate in their implementations, we suggest reading Architecture of a Database System by Joseph M. Hellerstein, Michael Stonebraker, and James Hamilton. Architecture of a Database System focuses mostly on Oracle, DB2, and SQL Server but does provide some insight into MySQL and PostgreSQL.
People have pointed out things we omitted and things we got wrong, so we have corrected some of these and will be slowly adding updates.
A lot of people have been making comments on the related Reddit Cross Compare of SQL Server, MySQL, and PostgreSQL thread in addition to this blog. I guess it shows people are really passionate about their databases. Lots of good discussion.
PostgreSQL 8.4 goodies in store
The PostgreSQL 8.4 planned release is March 1, 2009 and is outlined in the PostgreSQL 8.4 Development plan. It has just passed its May 2008 commit fest milestone and is currently in its July 2008 Commit Fest. Lots of PostgreSQL Planet bloggers have star
Weblog: Postgres OnLine Journal
Tracked: Jun 08, 20:06
More Database Comparisons
As many people who know us know we sit on several camps especially when it comes to databases. The camps we sit on are growing rather than shrinking. While we do have our favorites, we understand that peoples needs and comfort levels are different fro
Weblog: Postgres OnLine Journal
Tracked: Jul 19, 19:10
Cross Compare of PostgreSQL 8.4, SQL Server 2008, MySQL 5.1
Comparison of PostgreSQL 8.4, Microsoft SQL Server 2008, MySQL 5.1 In our May 2008 issue of Postgres OnLine Journal, we cross compared Microsoft SQL Server 2005, MySQL 5, and PostgreSQL 8.3. Some people mentioned well since 8.4 has now come out, shouldn
Weblog: Postgres OnLine Journal
Tracked: Aug 12, 20:12
BlogEngine .Net Database
BlogEngine .Net Database
Weblog: The Silence Of The LaMs
Tracked: Apr 01, 17:51
Tracked: Jul 12, 22:27
Tracked: Apr 26, 08:38
Tracked: Oct 25, 21:27
Recommended Books: PostgreSQL 8.4 Official The SQL Language MySQL Administrator's Bible SQL Server MVP Deep Dives
Display comments as (Linear | Threaded)
There are plans for MERGE for PostgreSQL 8.4 (if Simon Riggs writes it in time) + there is an example in docs on how to do UPSERT using PL/pgSQL function.
#1 PJ on 2008-05-14 01:31
You cant compare MSSQL vs postgres. I prefer to pay more for a good thing instead of having "FREE" chit...
#1.1 martin on 2010-09-29 13:23
I prefer paying less for a better thing and not being locked into a monopoly platform.
#1.1.1 Joseph on 2012-05-13 17:38
Man, are you sure the enterprise version of SQL Server cost 1 million? I think you're totally wrong, because I implemented it on our company less than 50.000 dollars. And I think the sollution become very mature. Try to inform yourself...
#2.1 Freddie on 2009-08-19 16:25
Lol! I think he was using hyperbole. Regardless, MSSQL is a great system for some companies, but it comes at significantly more up front cost when compared to the other two systems.
#2.1.1 andy on 2010-02-22 17:10
I'm sorry, but if you are going to present a case with such clear bias as "few but ramping up" on your underdog, while shitting bricks on any of the smallest flaws of the guy you dislike, you might as well be done with the fancy html tables and put a picture of a chimp flinging feces.
As an aside, I have to take your bait on the installation: if you call the setup for MSSQL difficult, then you are either outright lying or do not have the mental capacity to hit "Next>>" 5 times and enter a few names when prompted. You seriously saying this is difficult?
Oh, and dropping tables being a misfeature? Are you fucking retarded??
Get a life man, seriously, get a life and be done with your stupid bitterness.
#3 BLAHBLAH on 2008-05-14 05:54
You should learn to read. He clearly states why it is misfeature and he is right, dropping table which has objects (views in this case) depending on it is clearly a misfeature.
Also installation/maintenance is more then just hitting Next five times (all three dbs have installation wizard like that), you want to setup db for your specific use pattern, you want to make backups etc.
#3.1 PJ on 2008-05-14 06:19
Honestly BlahBLAH if that is your real name :) SQL Server is one of those databases we particularly love to work with. So if you think this is a complete bash, you are dead wrong. We are just pointing out things we would like changed in it.
You are forgetting that as PJ said there is more to supporting and maintaining a database than 5 keystrokes and if you think that is all, I feel pity for you. SQL Server 2005 requires you have .NET Framework 2 preinstalled, among other prerequistes. The amount of time you need to wait in between those 5 steps (and if you happen to be on a box that doesn't have those things downloading them) is a LOT MORE than you have to with PostgreSQL and MySQL. Troubleshooting when it actually fails is more work too (at least on Windows).
As we said there is nothing as sweet as the maintenance plan or tuning wizard that comes with SQL Server 2005 and those are things PostgreSQL and MySQL from our experience are sorely lacking.
If truth be known we make a ton more dough supporting SQL Server than any other database and we don't go around throwing poo at our cash cow for no good reason.
Our general philosophy if you care about something you should point out its faults as well as its strengths because otherwise you can't be taken seriously.
#3.2 Regina on 2008-05-14 07:11
The row for "Case-insensitivity" is confusing. What exactly do you mean by PostgreSQL "By default is case-sensitive and a pain to make it not so?" If you're talking about it being case-sensitive about identifiers that have been created as quoted-identifiers, e.g., CREATE TABLE tableX vs. CREATE TABLE "tableX" vs. CREATE TABLE "TableX", then MySQL and MSSQL (I presume) are not following the ANSI/ISO SQL Standard. PostgreSQL isn't following it entirely because the first CREATE should instantiate a table named "TABLEX" in its catalogs, but instead creates one named "tablex". What I found painful (but I haven't used it for a while) is MySQL's use of non-standard backticks for quoting identifiers.
Can you create a table named "user" or "USER" (or some other SQL reserved keyword) in either MySQL or MSSQL? In PostgreSQL you can create both and they can co-exist.
#4 Joe on 2008-05-14 08:37
Actually what we were talking about was text comparisons like
somefield LIKE '%abc%'
somefield LIKE '%ABC%'
In many cases you want those to be treated the same. Sure you could do ILIKE in PostgreSQL, but it doesn't use indexes and is not cross platform. Having to do upper upper is a pain too. Our point is that it should be controlled at the column level and SQL Server lets you do that. PostgreSQL does not let you do that.
#4.1 Leo on 2008-05-14 08:43
Maybe it's because I'm more comfortable on Unix (even though I've used VMS, DOS, Windows, etc.), but treating LIKE '%abc%' the same as LIKE '%ABC%', by default, seems unnatural to me.
#4.1.1 Joe on 2008-05-15 00:13
I think text comparisons in SQL databases are painful.
My solution is to create two columns where one would would suffice. The first column is the DISPLAY text, and the second column is the SEARCH_SORT text. The display text is whatever was entered. The search_sort text is a copy of the text stored as all UPPER or all lower case characters, pick one. My preference is lower case, but historically UPPER case is used. There are minor reasons to go with lower case, but now I'm getting off topic. If your SQL database likes indexes, then you might want to put one on the SEARCH_SORT text column. When you do a comparison, convert the desired search text to the same case as you are using in your search_sort column. Do the search using the text in matching cases. For example:
The search text might be "Katie" and so it gets converted to "katie". "Starts with" was chosen from the user interface so a % gets tacked onto the end of the LIKE search text. In my example, the text in the search_sort_name column is always in lower case, even though the name column retains any mixed case characters.
SELECT person_id, name
WHERE search_sort_name LIKE 'katie%'
There are even more gymnastics involved with the LIKE cause. The wildcard characters, different ways various SQL databases handle wildcard characters and embedded characters in the search text play havoc, but perhaps this is another topic also.
I hope that my techno-babble is useful to someone. ;-)
I must out of painfull experience speak against the point that mysql is case insensitive - it relies on the operating system, so importing and exporting between linux and windows tends to break everything.
#6 Tom Bille on 2008-05-14 10:14
"CREATE TABLE tableX ... should instantiate a table named TABLEX in its catalogs"
Like Oracle does it, then? If that's what ISO says, then the standard is wrong. It's a nuisance and strongly encourages AWFUL_NAMES_LIKE_THIS, since GoodTableNames become unreadable unless you quote absolutely everything.
#7 Bill on 2008-05-14 10:41
MySQL supports CREATE FUNCTION for SQL Functions. MySQL also supports User defined Functions and Aggregate Functions coded in C. Replication is Master->Slave but one server can take 2 roles. 5.1 also ships an Event Scheduler, but current version don't so it's ok to say "no".
You're missing some criteria, like Price, Sequence support, Online Backup...
#8 Nils on 2008-05-14 13:01
Nils - thanks for the correction, we'll update to reflect.
#8.1 Regina on 2008-05-14 13:17
Another good row might be XML/XPath support.
#9 Artacus on 2008-05-14 13:01
"Partial Indexes - e.g. you want to create a unique index but only consider non-null values"
You can do these in SQL Server with indexed views, which work even on the Express edition.
Indexes on views are enforced even when modifying the underlying table directly.
I gather that some of these products have some restrictions in their SQL; eg. no correlated subqueries? I'd like to see some info on that. Otherwise, the article is a good start. #2 is a foul mouthed idiot.
#11 TC on 2008-05-14 13:53
Actually all 3 support correlated subqueries so we didn't think to add that as a deciding point. Although since you mention it, I suppose its something that people wouldn't know and that is an important factor.
#11.1 Leo on 2008-05-14 14:21
What data type support? I'm more familiar with oracle on these, but I find it ridiculous that oracle still forces you to store data out of band (i.e., clobs) for more than 4000 characters, compared to pg's simple straightforward 'text' type. How do mysql and sql server stack up here?
Also, what about full-text search? Oracle has intermedia which is naturally extra cost but is pretty powerful. What do any of these three have?
#12 anonymous on 2008-05-14 14:26
PostgreSQL has tsearch2 for full text, at least more pwoerfull then MySQL's full text.
About case sensitive, most languages I know are case sensitive, unless you tell them not to do so. This goes for perl, php, java etc etc etc.... So I find it natural that in a DB it's also case sensitive.
The examples that Leo are given (LIKE '%ABC%' etc) are not anchored and will never be indexed by any RDMB, as far as I know. So Saying that ILIKE '%abcd%' cannot use an index is true for all databases. I strongly feel in such situations full text needs to be used unless table size is small.
Looking at the tabl,e I don't find this particular usefull... Just my opinion...
#12.1 Ries on 2008-05-14 16:14
Ries - I have to disagree with you on this one. In SQL Server 2005 - right down to the column level you can specify the collation, language and the sort order (dictionary, binary etc) and when you do a LIKE search it if you specify case insensitive dictionary (which is default), it will still use the index.
There are a lot of things databases do differently from programming languages e.g the way they short-circuit.
From a portability standpoint this makes SQL Server apps that utilize this feature (which is a lot) harder to port to PostgreSQL.
#12.1.1 Leo on 2008-05-14 16:40
Slight correction - LIKE 'ABC%' LIKE 'abc%' are index searchable but LIKE '%ABC%' etc are not. So you are right that was a bad example.
#12.1.2 Leo on 2008-05-14 16:49
#13 Mike McNally on 2008-05-14 16:52
We may add that to the pile but that may be a long discussion.
Both PostgreSQL and Microsoft SQL Server offer some form of clustered indexes (MySQL sort of does too, but I think it varies depending on storage engine and for example InnoDb, its always clustered by the primary key which neither PostgreSQL nor SQL Server require - so MySQL clustering works in a similar fashion to MSSQL). MSSQL's maintains the cluster index at a price (price is lower for SQL Server 2005 than it was for SQL Server 2000), while PostgreSQL requires a CLUSTER operation to maintain cluster. So on short side - MSSQL incurs a penalty during update/insert and in some cases having a CLUSTERED index on MSSQL may actually make non-covered index queries slower since all indexes are tagged against the clustered index. Dropping a clustered index also requires rebuild of all indexes in SQL Server since all are referenced to that.
PostgreSQL incurs a penalty for maintenance. There are also other differences such as MSSQL's use of covering indexes (which in many cases MSSQL will not need to go back to the actual table record - can read straight from an index if the query fields can be satisfied by the index).
So in short while PostgreSQL's clustering is less sophisticated it often performs better than SQL Server's. There are pros and cons to both.
#13.1 Regina on 2008-05-18 01:56
In MS-SQL / Transact SQL, you cannot drop a column, table or view if it is schema bound.
create table test_table
( a int not null )
create view test_view
select a from dbo.test_table
drop table test_table
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'test_table' because it is being referenced by object 'test_view'.
My opinion, given this comparasion is that SQL Server is the best because it is the most comprehensive and easier to use
#15 PAul on 2008-05-14 23:30
And why would there not be a truly free DBMS - FireBird?
#16 tene on 2008-05-15 04:47
Well PostgreSQL is truly free since it is BSD License too - you can modify and do not need to give back to the community if you don't want to and it is community controlled as opposed to single company controlled. I suspect FireBird is the same. Reading looks like FireBird is IPL licensed (InterBase Public License) which looks similar in spirit to BSD and it is controlled by a non-Profit Firebird Foundation.
We mentioned FireBird even though we haven't investigated it because of its choice of installation features - (SuperServer, Classic and Embedded). This puts it in an interesting spot because we see it as something with almost the lightness of SQLite but that can be spinned into a true server side db, but that is much more powerful than SQLite (e.g. it isn't type impoverished etc). As I recall it is also a one file db which makes it useful from a portability stand-point similar to SQLite. The embedded not requiring installation step is very alluring.
MySQL is just confusing in terms of licensing although arguably less confusing now that they are a part of Sun. They still need to work more on their community image since I get the impression its hard for non-MySQL employees to make direct contributions to the MySQL core code base.
#16.1 Regina on 2008-05-17 19:45
And why you don't have locking mechanism comparison?
#17 Milos Babic on 2008-05-17 06:46
We thought about that too, but we thought locking mechanism would open up a whole can of worms that not everyone would be able to appreciate unless they were deep into the trenches of the DB infrastructure. Comparing Locking mechanisms of the various dbs we felt to do it justice really needs a whole article dedicated to just that. Perhaps in another article we shall cover just that.
Along similar lines there is also the issue of how threads are spurn, page level compression etc.
#17.1 Regina on 2008-05-18 01:43
SQL Server can be tuned to take up less resources. The idea behind SQL Server is different than say, MySQL. SQL Server was architected to be the only thing on the box. It was designed to consume memory and handle scheduling on its own, so it could be more responsible. As a result, it will grab the resources unless you tell it not to. Now, is it is configurable as something like Oracle? No, it's not. But you can make SQL Server pretty low resource impact. SQL Server Express is actually designed around that concept.
"Can drop tables"
For SQL Server 2005 and above, you have more than schemabinding. You also have DDL triggers which can prevent ANY schema change.
MySQL has MASTER-MASTER replication in addition to MASTER-SLAVE replication. I have set this (M-M) up recently in my production environment and it works well. Clustering is also available.
#19 Kevin on 2008-05-23 10:38
What about MSSQL's DTS/SSIS and Reporting Services? What do the others have to offer or what is available?
How about realistic specification for a 1GB database, 250,000 records/rows and 50 users for an idea of costs and possibly scalability? We all know the minimum specs but they are never real world. (You mention Firebird but I wonder how truly scalable it is.)
Perhaps even a unique selling point and major bugbear, if I'm to leave my MSSQL environment I need to know what's great and what's not?
Best Linux (supported?) distributions for the open source products. I acknowledge that this could be contentious.
Sometimes costs and lack thereof are just not enough.
(By the way, this is the best doc that I've found so far on MSSQL vs MySQL and PostGreSQL, so thanks for your efforts.)
#20 Marcus Anderson on 2008-06-24 06:31
Thanks for the complements. Regarding Reporting Services and SSIS. There is no equivalent packaged in with MySQL or PostgreSQL.
For most of our development - Reporting Services is overkill since it requires all sorts of installation so we usually develop with ActiveReports.NET which works equally well with all databases and allows deploying reports as simple .NET assemblies or in a web App_Code folder without installation. Plus the great thing about it which I have had real frustration with in Reporting services is I can use the same reporting template for various reports. The template can be stored as just an xml file similar to reporting services RDL. We can roll our own authentication scheme easily which seems much harder to do with reporting services. Great for web development.
SSIS - there are alternatives that people use - e.g. some people swear by Scribe and say Scribe is better than SSIS (not sure how much it costs). Personally I've only used SSIS and rolled my own because it was easier to manage so can't speak for Scribe. I actually find SSIS terribly annoying to work with because you need to create your package and then load it into SQL Server if you want it to run as a managed job and when you need to edit it, pull it down etc. I really hope they bring back the ease of being able to edit a package directly like you could with DTS where you could do it straight from enterprise manager. But I digress.
Then there is AutoMate which one of our clients uses and swears by as a much easier and powerful alternative to DTS and SSIS. I think that works with anything you throw at it and has a lot of overlaping functionality with SSIS. If you are not a core .NET developer I think you'd feel more comfy with AutoMate or Scribe.
Then there is always the argument - if you really like SSIS and Reporting Services - just buy the minimum SQL Server that will get you a license for those things. Those work just fine with MySQL and PostgreSQL and I've got one project where I have to report against both SQL Server and PostgreSQL and use reporting services for both.
Then there is scalability. For large databases - e.g. over 20 gigs and many connections, I've been pretty impressed with PostgreSQLs performance. It actually performs better than SQL Server 2005 Standard in most cases (for loading speed and querying speed).
MySQL we have been disappointed with its query optimizer and so don't like it for analytical intensive applications. I think they are trying to improve that in the 6.0 version.
We tend to do a lot of rollup and GIS spatial analysis queries (which is non-existent in SQL Server 2005 and pretty poor in MySQL) and things of that sort so depending on your workflow you may have different experience.
#20.1 Regina on 2008-06-27 00:34
Thanks Regina for your comments.
Your point on using a lower version of SQL Server for the Reporting Services and SSIS is interesting and I must admit to having not considered. It is now another line of investigation! :)
Our requirements for SSIS are basic. We perform basic database replication to other MSSQL 2k servers using views and scheduled DTS scripts (we still have MSSQL 2k). Our databases are comparatively small (c. 1GB) and so are efficient enough. The document did not reference anything about data import/export and for the greater good I asked a generalised question.
Likewise for Reporting Services, I see a great potential to reduce the amount of effort that we make on standard and regular reports for the business. Having our regularly scheduled reports automatically execute and their results retained would serve us enormously (schedule to run out of hours, senior staff can access old reports, etc.). Similarly empowering our senior staff to execute their own reports as and when they require it would benefit them as well as IT. (I would never allow directors and/or our clients to make their own reports from our data - it is too easy to misinterpret any results.)
#20.1.1 Marcus Anderson on 2008-07-01 05:46
>Support life cycle
>Total cost of ownership over (say) 10 years on a typical system - in my case 50 tables (lots of referential integrity), 40 clients, a php web portal and a mirrored DB
I'm probably an average joe developer currently developing a factory management system and got a test system in SQL Server 2005 now making the decision to keep 2005 or move to MySQL (which would be lower licencing costs for the web clients and the numerous factory touch screens) the questions above are what I am currently asking. I develop in Delphi, php (and C for low-level stuff) and I am comfortable with both MSSQL and MySQL but only really uses standard SQL and table views (I guess I'm a bit old fashioned!) thus I never really get too locked ind. SQL server 2008 looks nice on paper but I worry about breaking changes between versions! I've never tried postgress yet (things to do!)
Can add columns and change names, data types of views without dropping
The entry for PostgreSQL as "NO" is not true -- at least for 8.3 and I think even earlier in the 8.X series. I just did an experiment on a table that had data in it and I changed column names and column data types (I used pgAdmin3). There are some limitations -- you can't change a text column to a numeric format, but there the options are pretty good.
#22 Bill on 2008-10-28 12:00
Bill in your example, was your table used in a view?
Our point was not that you can't change data types of columns and names of columns in a table, but that if that table column is used in a view, it won't let you. Actually you can change data types of columns in a table even if the table is used in a view as long as the column is not used in a view.
This is not entirely a bad thing - since I just had some frustration with MySQL where it let me rip out tables used in views and so forth and then phpmyadmin wouldn't load without me going in and repairing the views. Thats neither here nor there. I can see pros and cons to both.
One other nice thing about PostgreSQL is that if you rename a table, it corrects the views that use that table.
#22.1 Regina on 2008-10-29 08:06
Graphical View Designer (e.g. you can see tables and select fields drag lines to do joins) included no additional charge
It's worth noting that there are many third party relational viewers available at no charge. One of them is Open Office Base, and can connect to SQL Server, MySQL, and PostgreSQL.
#23 Bill on 2008-10-28 12:13
A nice comparison, thanks.
I think a simple case to show the differences in the locking strategy is the question "do writers block readers" which can easily be tested using two concurrent connections. One does
UPDATE employees SET salary = salary * 1.1;
whereas the other does
SELECT * FROM employees;
(before the first one commits, and of course with autocommit turned off)
An interesting question (though nearly impossible to put in a simple comparison) is how things scale when dealing with a large number of concurrent reads and writes.
There is an interesting comparison about the scalability on different platforms between MySQL and Postgres here:
#24 Thomas on 2009-01-29 17:51
Thanks for the complements. Regarding Reporting Services and SSIS. There is no equivalent packaged in with MySQL or PostgreSQL.thank you very much for informing to me .good work :)
"SQL Server's deprecated timestamp which is a binary"
Where did you get the info that TIMESTAMP datatype is deprecated in SQL Server? I'm pretty sure it isn't.
#26 chojrak11 on 2009-04-18 14:11
Lots of places. It got superceded by rowversion.
Here for example:
The util that keeps a counter of use of deprecated features one of the counters is timestamp data type count:
Total number of times the deprecated timestamp data type was encountered in a DDL statement. Use the rowversion data type instead.
I think the main reason it was deprecated is that its confusing. when other databases use the term timestamp it is meant to mean a datetime not some binary glob thing to keep track of versions.
#26.1 Leo on 2009-04-18 16:08
"Timestamp" on SQL Server is not only confusing, it's totally useless except for replication agents. It's not a time, and therefore not useful at all to the application programmer. Their datetime type is not a viable replacement either, because it doesn't update automatically: what if an application is coded poorly and doesn't update the time when modifying records? What if one application running in the U.S. and one running in Japan both use local times? It's not just a matter of a query that can be fixed - your data is screwed.
MySQL's timestamp datatype, OTOH, is *really* slick, it automatically updates the time any time the record is changed (not just any time you do an update query - you actually have to change the record), and the updates are happening on the server side, so timezones aren't an issue.
(Postgres might also do this, I've never used it so I don't know.)
Coming from a MySQL background into a SQL Server environment, Microsoft's implementation of the timestamp type has been a source of a great deal of frustration (in fact just about anything on SQL Server is frustrating after having experienced the simplicity, efficency, and elegance of MySQL)
Now that Postgres 8.4 has been released you might want to update the entry for Windowing functions (PG's support is far better than SQL Server's now)
And you might add common table expressions to the overview which is supported by SQL Server and Postgres alike (although not 100% along the standard by SQL Server)
#27 Thomas on 2009-07-04 04:37
Thanks for pointing htat out. We'll be amending this in a bit. We are planning to add in CTE too, but are planing a bigger comparison of that.
In case you haven't noticed, we just completed an entry comparing the windowing functionality between PostgreSQL, SQL Server 2008, IBM DB2, and Oracle
#27.1 Leo on 2009-07-05 03:47
Very useful! I've been looking forward to move to PG from MySQL and now I'm sure of the choice :)
Very good article!
To migrate data across various database systems(e.g. SQL Server, MySQL, PostgreSQL, Oracle, IBM DB2, Access, Excel), using ESF Database Migration Toolkit.
Martin, you could be using MyISAM as your table engine, InnoDB offer better DB reliability options.
MS SQL has "no support of timezone or just plain DATE"
Isn't it named smalldatetime? It's available even on much older SQL versions.
Also, no one mentions the reliability.
I have both MS SQL and MySQL. I have never had to restart MS SQL and/or encountered data corruption while MySQL proved to be more fragile. Yes, it's expensive and close-source, but it's a trade-off.
Thanks for the comparison table BTW.
#30 Denix on 2010-03-17 14:57
No -- DATE type is not supposed to have time in it. This new type wasn't introduced into SQL Server until SQL Server 2008. The smalldatetime of prior versions is a date with time with no concept of timezone (it just has more of a limit on the Year range than datetime). So the best you can do is assume the timezone is what you are currently in. Which is problematic if you are dealing with data from all over the world and want the data type to reflect the timezone.
As far as the MySQL/MSSQL case, I think that is fairly subjective. MySQL innodb for example you won't get as much corruption, but I haven't had issues with MyISAM either. I think if you have a bad drive, recovering from MYISAM may be harder. I have had issues with MSSQL, but I don't blame that on MSSQL. I blame that on the fact that we use MSSQL a lot and for more high end stuff than MySQL so we are bound to have more issues.
PostgreSQL has been pretty rocksolid for us on both Windows and Linux. But on the downside if you have a crash with MySQL MyISAM -- seems much easier to throw back the missing files and be done. No messy consistency checking going on or playing back logs etc.
The MyISAM structure is relatively trivial which makes it in some cases easier to fix when you have major damage since you can throw piees togehter from backup. Which depending on which side you are looking at is a feature/misfeature. :).
So see what I mean subjective. If we made any sweeping statements about any, people will call us on it because it would all be pretty anectdotal.
#30.1 Regina on 2010-03-18 02:45
For the case of stability, maybe on Windows MySQL is less stable than the others (Specially MSSQL). I used to work on Windows Server 2008 and MySQL 5.0 (25~50 simultaneous connections, 5 different databases and total data of about 500 MB) and it crashes a few times (Windows also crashed some times :-(). I changed to a Linux Ubuntu Server, and in the same condition my company has been working for one and a half year and the servers (Ubuntu and MySQL) crashed 0 (zero) times. So the problem is in the database server or the OS server? :-)
T-SQL RowNumber() vs Rank() vs DenseRank() vs NTILE() | SQL Lion
SQL Server comes with some magical words that make simple the life of a developer or DBA to a great extent. And these magic words are RowNumber(), Rank(), DenseRank() and NTile(). Follow the link to get more…
Anyone have complete global comparison benchmark pie/bar charts for all these database apps all in one like:
Im looking for functionality in PostgreSql, that will do the same as SqlDependency objects in Microsoft SQL-server 2005. Is this possible ?
Haven't played around with SQLDependency to know how exactly it works and also haven't played around with what I think is the PostgreSQL equivalent - which I think is: PostgreSQL SQL Notify. Take a look at that and see if it does what you need:
#34.1 Regina on 2011-04-04 07:00
Sadly the functionality does not look like being the same... Its a shame - I think it prevents alot from migrating.
Hi! everyone, I've to develop a small retail store management application for our Store. I've selected the front end as JAVA and back end is ??? (Still confusing also) 1.Using File Handling(but i don't have experience in this and Complicated too) 2.Oracle 3.MySql 4.MS Access 5.Ms Excel files as a database.
Can anyone suggest me which one is light weight and higher performance for small database, with able to work within 1GB of RAM.
Thanks for read it,will you help me..
#35 jeeva on 2011-09-17 11:25
I would stay away from Oracle. It's not known well for working with low memory.
You probably also want something that has a good jdbc driver if you are building your front-end in java. So I would cross MS Access and Excel off your list.
That leaves you with
PostgreSQL, MySQL, Firebird, SQLite (I think SQLite has a jdbc driver though not sure since we only use it in PHP and .NET web apps), SQLServer Express.
All the above have minimal memory requirements. PostgreSQL,MySQL, and firebird have better scaling than SQLite if your database grows. SQLServer Express has too many .NET dependencies so a pain to install, so I would stay away from that. Then there is licensing. If this is a product you sell and need to package with your software, I would stay clear of MySQL since the Dual licensing is too confusing and gets really messy if its software you need to distribute to clients.
There are also java only databases like HSQLDB though haven't used that but if you are married to Java, might be a good option. Downside is that it doesn't have as nice of support with other tools.
#35.1 Regina on 2011-09-19 00:07
Great article... But why such unprofessional terminology/wording in the Microsoft SQL Server column? That completely ruins the credibility of this comparison... Sad.
#36 RS on 2012-01-27 06:14
Syndicate This Blog
Show tagged entries