Diwali Deal : Flat 20% off + 2 free self-paced courses + Free Ebook + $200 Voucher  - SCHEDULE CALL

- Oracle DBA Blogs -

Difference between Oracle, SQL Server, MySQL and PostgreSQL

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:

Read: Top Oracle DBA Interview Questions and Answers

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. SQL Server Curriculum 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. SQL Server quiz 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.

Read: Oracle Database Tutorial: Learn Oracle DBA From Scratch

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.

Read: Oracle- Perfect Choice For The Success of an Organization

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. free SQL Server demo 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.



fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

1 day 15 Nov 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

0 day 14 Nov 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

6 days 20 Nov 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

1 day 15 Nov 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

1 day 15 Nov 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

8 days 22 Nov 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

4 days 18 Nov 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

2 days 16 Nov 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

16 days 30 Nov 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

9 days 23 Nov 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

1 day 15 Nov 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

2 days 16 Nov 2024

Search Posts

Reset

Receive Latest Materials and Offers on Oracle DBA Course

Interviews