Two major database types are SQL and NoSQL, that is, relational and non-relational. Choosing the correct type will greatly influence how you develop and manage your data.
Relational Databases use SQL which is a standard and widely used database language.
A relational database specifies relationships in the form of tables.
SQL allows you to easily perform CRUD (create, read, update, and delete) operations.
NoSQL are non-relational database systems dedicated to storing very large data sets.
These databases do not require a fixed schema and are therefore easy to change and scale.
NoSQL covers a wide range of database technologies that store structured, semi-structured, unstructured, or polymorphic data.
SQL was developed in the 1970s.
Is highly structured and focuses on reducing data duplication.
NoSQL was developed on late 2000s.
Is less structured and focuses on scaling and flexibility by allowing rapid changes.
SQL databases are structured and have a pre-defined schema (data model).
The schema is designed so that it accomodates the business requirements.
Once created, all data must follow the table layouts and other structures.
Changing the data model is difficult and disruptive to the entire system.
NoSQL databases have dynamic schemas and unstructured data can be stored in multiple ways.
The storage model can be column-oriented, document-oriented, graph-based, or key-value pairs.
This means that documents do not need to follow a defined structure.
Each document can have its own unique structure and fields can be added as you go.
SQL databases are vertically scalable.
This means that a single SQL server can increase its load by adding more CPU, RAM, or SSD capacity.
NoSQL databases are horizontally scalable.
This means that additional servers can be added by sharding which lets you handle higher volumes.
This is the preferred scaling method for large and frequently changing data sets.
Write CRUD queries (
UPDATE, etc) to access and manage SQL databases.
A direct database connection is needed to construct queries.
NoSQL databases can be accessed using REST APIs.
Performing CRUD operations varies depending on the platform.
Microsoft SQL Server
Allows you to write queries with great flexibility.
Supports diverse workloads and allow query engine to optimize queries according to requirements.
With the use of normalization and optimization, a reduced footprint maximizes database performance.
It follows ACID (atomicity, consistency, isolation and durability) properties that guarantees accurate and valid transactions.
Requires careful up-front database design before developing applications.
Changing the data model in a production database can be difficult leading downtime.
SQL Server is limited in terms of horizontal scaling.
This can be mitigated by replication and failover techniques.
Supports dynamic schemas with flexible data models
Dedicated to unstructured data which enables unique structures for different data.
Limited built-in database functionality results in high database performance.
Provides high-level APIs for powerful data structures.
Supports seamless horizontal scalability without a single points of failure
Does not support indexing, therefore searching can be slow, especially with large data sets.
JOIN and retrieving related data is inefficient.
It has vague interpretations to no support with respect to the ACID properties.
There is no possibility for NoSQL engines to optimize queries.