Comparison of MySQL, Postgresql, MS SQL Server 2000
Here is a comparison of these 3 database servers. The versions compared are MySQL (4.x), Postgresql (8.x), MS SQL Server 2000. In MySQL 5 version, many useful enterprise database features are expected and hence some feature discussion for MySQL refers to version 5 and use of specific engines.
The information below is a quick comparison collated in one place from the internet. It should be taken as a first level guide for further decision making in choosing the right database for one's needs.
|
|
MySQL |
Postgresql |
MS SQL
Server 2000 |
Note |
|
|
|
|
|
|
|
Views |
No
Support |
Non
Standard Support |
Supported |
|
|
|
|
|
|
|
|
Select |
|
|
|
|
|
|
|
|
|
|
|
Order
result set |
Can be
done |
Can be
done |
Can be
done |
ORDER BY
key is not part of sql standard, but most d/b support it |
|
|
|
|
|
|
|
Limiting
result set |
Can be
done |
Can be
done |
Can be
done |
This
means say selecting 10 rows only of a result set which returns 1000 rows |
|
|
|
|
|
|
|
Top n
query |
Can be
done |
Can be
done |
Can be
done |
Give top
n results of a query |
|
|
|
|
|
|
|
BOOLEAN
type |
Can be
done |
Supported |
Can be
done |
|
|
|
|
|
|
|
|
CHAR
type |
Not good
support |
Recommeded
varchar |
Supported |
|
|
|
|
|
|
|
|
TIMESTAMP
data type |
Bad
support |
Supported |
Supported
using DATETIME |
|
|
|
|
|
|
|
|
CHAR_LENGTH() |
Supported |
Supported |
Supported
using LEN and DATALENGTH |
|
|
|
|
|
|
|
|
SUBSTRING |
Supported |
Supported |
Supported
using diff name |
|
|
|
|
|
|
|
|
TRIM() |
Supported |
Supported |
Can be
done using LTRIM and RTRIM |
|
|
|
|
|
|
|
|
LOCALTIMESTAMP |
Supported |
Supported |
No
timezone information |
|
|
|
|
|
|
|
|
||
concatanate operator |
Standard
broken |
Supported |
Need to
use + instead of || |
|
|
|
|
|
|
|
|
UNIQUE
constraint |
Supported |
Supported |
Supported |
|
|
|
|
|
|
|
|
auto
increment |
All d/b
have own syntax and varying support for IDENTITY standard syntax |
|
|
|
|
|
|
|
|
|
|
Transactions |
Yes |
Yes |
Yes |
|
|
|
|
|
|
|
|
Foreign
key |
Yes |
Yes |
Yes |
|
|
|
|
|
|
|
|
ACID
compliance |
Yes |
Yes |
Yes |
|
|
|
|
|
|
|
|
Clustering |
Yes |
No |
Yes |
|
|
|
|
|
|
|
|
Views |
planned
5.x |
Yes |
Yes |
|
|
|
|
|
|
|
|
Stored
procedures |
planned
5.x |
Yes |
Yes |
|
|
|
|
|
|
|
|
Cursors |
planned
5.x |
Yes |
Yes |
|
|
|
|
|
|
|
|
Triggers |
planned
5.x |
Yes |
Yes |
|
|
|
|
|
|
|
|
Tablespaces |
Yes |
Yes |
to check |
|
|
|
|
|
|
|
|
OS
support |
All |
All |
Windows
only |
|
|
|
|
|
|
|
|
User
defined data types |
No |
Yes |
to check |
|
|
|
|
|
|
|
|
Data
partitioning |
No |
No |
Only
Range partitions |
Another
web page said MySQL supports partitioning! |
|
|
|
|
|
|
|
|
|
|
|
|
|
User
defined data types |
Yes |
Yes |
to check |
|
|
|
|
|
|
|
|
Replication |
Yes |
Yes |
Yes |