Oracle, SQL Server, MySQL 5 and PostgreSQL are the most commonly used databases and people normally get caught in the comparison between them. The inability to work around all aspects of comparison becomes a limitation and leads to indecisiveness about which database to use.
Here is a comparison of the all the four in a pointwise manner:
Microsoft SQL Server is relational DBMS by Microsoft but both MySQL and PostgreSQL are widely used open source RDBMS.
Primary Database Model: A database model is a series of concepts which are used to describe data, its relationships, constraints, and even the semantics. It thus decides the logical makeup of any database. It also determines the manner in which data can be stored, organized and further used. The most widely used database model is the relational database model which is based on a table-based format. All basically use the Relational Database Management System which supports the relational data model.
Secondary Database Model: It is a vital element of comparison.
- Document Store: Document stores are common to all. They are also called database systems which are document-oriented and are known for schema-free data organization. Latter basically means that there is no uniform structure of records and can have a nested form.
- Key Store: Another common feature is the key-value store which basically stores keys and values in pairs. This is a simple database system which is not considered appropriate for complex applications. But mostly the simplicity is the primary aspect which makes it more attractive in some special instances.
- Graph DBMS: Another notable attribute which is only seen in Oracle and Microsoft SQL Server is the Graph DBMS. Latter is also known as a Graph Database. Herein, the database is represented in graph structures as nodes and edges which represent the relationship between nodes. The processing of data becomes easier with simple calculations of particular graph properties. Indexing on all nodes is not really provided.
- RDF Store: In the secondary model, RDF stores is seen in Oracle. It is basically a method for describing the information which was originally developed for detailing the metadata of IT resources.
Ranking:
As per the latest ranking in January 2019, Oracle has the highest score and is ranked number 1. This is followed by MySQL, then Microsoft SQL and ultimately PostgreSQL. While the first three shares close scores the last one has a wide gap in scores.Server Operating System: This has a huge variation when it comes to a comparison of the four databases. Below is a list of the supported OS by all four individually:
- Microsoft SQL Server: It supports Linux and Windows.
- Oracle: It supports AIX, HP-UX, Linux, OS X, Solaris, Windows, z/OS.
- PostgreSQL: It supports FreeBSD, HP-UX, Linux, NetBSD, OpenBSD, OS X, Solaris, Unix and Windows.
- MySQL: It supports FreeBSD, Linux, OS X, Solaris, and Windows.
Supported Languages for Programming: A programming language is a structured and organized language which comprises various instructions for producing various kinds of outputs. They are basically used in computer programming for the creation of programs which go to implement certain algorithms. Here again, there is a huge variation seen. Oracle has by far seen to be supporting a maximum number of languages followed by MySQL.
- Microsoft SQL Server: It supports C#, C++, Delphi, Go, Java, JavaScript, PHP, Python, R, Ruby, Visual Basic
- Oracle: It supports C, C#, C++, Clojure, Cobol, Delphi, Eiffel, Erlang, Fortran, Groovy, Haskell, Java, JavaScript, Lisp, Objective C, OCaml, Perl, PHP, Python, R, Ruby, Scala, Tcl, Visual Basic.
- PostgreSQL: It supports.Net, C, C++, Delphi, Java, JavaScript, Perl, PHP, Python, and Tcl.
- MySQL: It supports Ada, C, C#, C++, D, Delphi, Eiffel, Erlang, Haskell, Java, JavaScript, Objective-C, OCaml, Perl, PHP, Python, Ruby, Scheme, and Tcl.
Licensing: This forms an important criterion of comparison. The Microsoft SQL Server uses Commercial licensing which is closed source and includes features in various levels resting on version and Free Crippleware. Licensing for MySQL is Open Source, Commercial while for PostgreSQL it is also BSD Open Source. In Oracle, people can opt from a number of database services which rest on Oracle Cloud.
Availability of ODBC, JDBC, ADO.NET drivers: These are vital for an aspect of Data Connectivity but are generally overlooked. They can significantly enhance the performance of application, reliability, and portability. All these are powerful, economical and easy. These are supported by all the databases in comparison.
Installation and Maintenance: There are a lot of variations seen. While Microsoft SQL Server is the hardest to install and consumes a lot of time. It is easiest in case of MySQL and of medium level of difficulty for PostgreSQL. In case of Oracle as well, it is hard.Can columns be added or Change names or data type views without dropping: This can be easily handled in both Microsoft SQL and MySQL but can be highly bothersome in case of PostgreSQL especially in case of views which depend on other views.
Drop Tables: This is possible in both Microsoft SQL and MySQL but is not possible in PostgreSQL. This is also available for Oracle.
Computed Columns: This is possible in Microsoft SQL Server. This is less often used. Only in the case when you need the computed columns indexed you use this. Computed Columns are actually limited in use. This is, however, not possible in MySQL and PostgreSQL. In latter, however, there are functional indexes. In MySQL, it might feature in some future release. In Oracle, there was a complete absence of this feature but in 11g, a new feature lets you create a ‘virtual column’ which is basically an empty column and has a function over other columns of the table.
Functional Indexes: These indexes let you make an index based on a particular expression or function. These can have many columns, arithmetic expressions or even a PL/SQL function. These are absent in both Microsoft SQL and MySQL but are present in PostgreSQL. In Oracle, they were introduced in Oracle 8i.
Partial Indexes: These can be created by adding the Indexing Partial clause. The feature was completely absent in Microsoft SQL Server and MySQL but is present in PostgreSQL and Oracle. Oracle emulates partial indexes in a unique way.
Foreign Key: A foreign key which has a cascade delete actually means that if any record in the parent table gets deleted then the corresponding records in all the child tables will get deleted by default. This is present in Microsoft SQL, PostgreSQL and Oracle as well. In MySQL, this is present as InnoDB and not MyISAM.
Multi-Row Value Insert: This is not present in Microsoft SQL but is seen in all other three. The INSERT ALL statement in Oracle is used for adding many rows with only one statement.
UPSERT Logic: This is not possible in Microsoft SQL Server and PostgreSQL but is present in MySQL and Oracle. This is basically a feature where you can both insert and update together, hence the name UPSERT.
Programming of procs or functions in many languages: This is present in all but in MySQL although it is being developed in the same. While in Microsoft SQL Server, it is possible for any language which complies with CLR but it has to be compiled into a dll first. In PostgreSQL, the language environment is hosted by the downside server.
Dynamic and Action SQL in functions: This feature is not there in both Microsoft SQL Server and MySQL. In both you can however, it can be done in Stored Procedures. In PostgreSQL and Oracle, the feature is present.Date Time Support: Although the feature is present in all four it is best in PostgreSQL. It comprises Date, TimeStamp and TimeStamp with Timezone.
Support Creation of Functions: This is present in all four databases. Authentication: This stands for verification of the identity of someone who needs to access data or resources.
- Microsoft SQL: It has standard Db security and NT/Active Directory Authentication
- MySQL: It also has standard Db security with table-driven security.
- PostgreSQL: It has extensive security with standard, LDAP, SSPI, PAM, trust etc.
- Oracle: Standard Db security
Case-insensitivity: This basically means the databases are sensitive to cases. Apart from PostgreSQL none of the other databases are case-insensitive. Only PostgreSQL is case-sensitive by default
. Conclusion:
All the four databases offer unique features which have great usability for different audience. Microsoft SQL Server is the most preferred choice by many and thus will have great potential in 2019. A planned and systematic training course can open many new opportunities for you. JanBask Training offers state of the art and a comprehensive training course which you can tailor to your needs. It involves a hands-on approach to give you a practical feel of the same and acts as a great confidence booster.