SQL Vs NoSQL

Are you having lots of queries flooding your mind when you think about SQL and NoSQL? Then this article gives you a complete run down on the major differences between the two, and their benefits.

 1. What are the Key differences between SQL and NoSQL?

SQL	NoSQL
SQL Databases are called Relational databases or RDMS – Relational Database Management Systems.	NoSQL Databases (DBs) are called Non-Relational or Distributed Databases.
Data Storage is based on a single data model – relational model. This model is table based i.e. Consisting of rows and columns of data.	Data Storage is not based on a single data model. Most outstanding ones are key-value pair, graph, document, and columnar.
SQL DBs use only SQL as the language for querying. The syntax of SQL is standard across any database.	NoSQL DBs use UnQL (Unstructured Query Language) for querying. The syntax of using UnQL varies from database to database.
Data stored as per fixed schemas. Each row must have data specific to a column.	Schemas are dynamic. Each row need not have data for each column.
Database scaling is possible with vertical scaling i.e. more data storage requires a bigger server. Though multiple RDBMs servers can be added, this is complex and time consuming.	Database scaling is possible with horizontal scaling across multiple servers.
SQL Databases follow ACID ((Atomicity, Consistency, Isolation, Durability) properties to ensure that database transactions are reliable.	NoSQL database follows the Brewers CAP theorem (Consistency, Availability and Partition tolerance).
SQL Databases get classified as either open source or non-open source.	NoSQL DBs get classified based on the data storage type- Graph DBs, Key-Value store DBs, and Document store DBs.

2. SQL Vs NoSQL – Which to Choose?

SQL Vs NoSQL does not mean that you can stop using SQL. Opting for NoSQL does not imply that it is better than SQL. The choice between SQL and NoSQL depends broadly on the following:

Type of data that you need to store.

• If data type is changing rapidly or not.
• Growth factor of data size.
• Volume of data.

Choose SQL	Choose NoSQL
If your data is within the limits of relational database.	If your data is fringing on the limitations of relational database or NoSQL databases are highly preferred for large data sets (i.e. for big data).
If your data operations demand high transactional rates or complex data queries needing control over execution plans.	If the scale of your data operations is vast and needs scaling.
If your data is structured and can be stored with pre-defined schemas.	If your data is unstructured or is a combination of both structured & unstructured.
If your goal is to support isolated indivisible transactions whose changes are persisted and leave the data in a consistent state.	If your goal is to support only either isolated indivisible transactions whose changes are persisted or leave the data in a consistent state.
If changes to data are not too many.	If changes in data are inevitable.

3. What are the strengths and weak points of SQL?

Strengths	Weak Points
SQL is powerful for supporting database CRUD (Create, Read, Update, and Delete) operations and is an industry-standard.	Since the data is stored in relational tables, there is a performance bottleneck of operations that span a lot of tables.
SQL databases employ pre-defined optimizations like column index definitions to help speed up query operations	Though SQL databases can scale up vertically insanely high, eventually there will be an upper limit and vertical scalability is expensive.
	SQL databases are not best fit for hierarchical data storage.

4. What are the Strengths & Weak points of NonSQL?

Strengths	Weak-Points
NoSQL database fits better for the hierarchical data storage as it follows the key-value pair way of storing data similar to JSON data.	NoSQL don’t have standard interfaces to perform complex queries, and the queries themselves in NoSQL are not as powerful as SQL query language.
NoSQL DBs have simpler access patterns.	NoSQL for transactions purpose, it is still not comparable and stable enough in high load and for complex transactional applications

SQL Database Examples

MySQL Community Edition

MySQL database is very popular open-source database. It is generally been stacked with Apache and PHP, although it can be also stacked with nginx and server side Java scripting using Node.js.

The following are some of the benefits and strengths of MySQL:

• Replication: By replicating MySQL database across multiple nodes the work load can be reduced heavily increasing the scalability and availability of business application
• Sharding: MySQL sharding is useful when there is large number of write operations in a high traffic website. By sharding MySQL servers, the application is partitioned into multiple servers dividing the database into small chunks. As low cost servers can be deployed for this purpose, this is cost effective.
• Memcached as a NoSQL API to MySQL: Memcached can be used to increase the performance of the data retrieval operations giving an advantage of NoSQL API to MySQL server.
• Maturity: This database has been around for a long time and tremendous community input and testing has gone into this database making it very stable.
• Wide range of Platforms and Languages: MySQL is available for all major platforms like Linux, Windows, Mac, BSD and Solaris. It also has connectors to languages like Node.js, Ruby, C#, C++, C, Java, Perl, PHP and Python.
• Cost effectiveness: It is open source and free.

MS-SQL Server Express Edition

It is a powerful and user friendly database which has good stability, reliability and scalability with support from Microsoft. 
The following are some of the benefits and strengths of MS-SQL:

• Integrated Development Environment: Microsoft Visual Studio, SQL Server Management Studio and Visual Developer tools provide a very helpful way for development and increase the developer’s productivity.
• Disaster Recovery: It has good disaster recovery mechanism including database mirroring, fail over clustering and RAID partitioning.
• Cloud back-up: Microsoft also provides cloud storage when you perform a cloud-backup of your database

Oracle Express Edition

It is a limited edition of Oracle Enterprise Edition server with certain limitations. This database is free for development and deployment. The following are some of Oracle benefits and strengths:

• Easy to Upgrade: Can be easily upgraded to newer version, or to an enterprise edition.
• Wide platform support: It supports a wide range of platforms including Linux and Windows
• Scalability: Although the scalability of this database is not cost effective as MySQL server, but the solution is very reliable, secure, easily manageable and productive.

5. Name some NoSQL DBs:

Database Name	Features	Benefits & Strengths
MongoDB	It is non-relational database with dynamic schema. It is written in C++ language. It stores data in JSON like documents. Querying is done using a single document which eliminates the join operations.	Speed: For simple queries, it gives good performance, as all the related data are in single document which eliminates the join operations. Scalability: It is horizontally scalable i.e. you can reduce the workload by increasing the number of servers in your resource pool instead of relying on a standalone resource. Manageable: It is easy to use for both developers and administrators. This also gives the ability to shard database Dynamic Schema: Its gives you the flexibility to evolve your data schema without modifying the existing data.
Couch DB	CouchDB is an acronym for Cluster Of Unreliable Commodity Hardware & is a document based NoSQL database. A document-oriented database designed for Web applications. JavaScript is its query language, using MapReduce-and HTTP for an API – using your web browser. Scaling is done with horizontal partitioning of database.	Schema-less: As a member of NoSQL family, it has a dynamic schema which makes it more flexible, having a form of JSON documents for storing data. HTTP query: You can access your database documents using your web browser. Conflict Resolution: It has automatic conflict detection which is useful while in a distributed database. Easy Replication: Implementing replication is fairly straight forward.

Hadoop’s NoSQL Database:

Apache HBase is the open source implementation of Google’s Big Table, with slight modifications.  It was created in 2007 , it was initially  a contributions to Hadoop  and it  later became a top level Apache project.
Apache HBase is a distributed column-oriented database built on top of the Hadoop file system  and it is horizontally scalable meaning we can add the new nodes to Hbase as data grows.
It is well suited for sparse data sets, which are common in many big data use cases.
An Apache HBase system comprises a set of tables. Each table contains rows and columns, much like a traditional database. Each table must have an element defined as a Primary Key, and all access attempts to HBase tables must use this Primary Key.
It is a part of the Hadoop ecosystem that provides random real-time read/write access to data in the Hadoop File System.