SQL and noSQL

From analysts and engineers to IT decision-makers, many are familiar with Relational Database Management Systems (RDBMS), and the Structured Query Language (SQL) used to interact with them. While these terms refer to a decades-old paradigm that remains a wide standard, database systems’ sheer variety and depth can be dizzying today. What’s more, rising volumes of unstructured data, availability of storage and processing power, and evolving analytic requirements have generated interest in fundamentally different technologies.

These popular alternatives to traditional RDBMSs show promise for various modern use cases, collectively known as NoSQL.

To make informed decisions about which to use, practitioners should be aware of the differences between SQL, NoSQL, individual Database Management Systems (DBMS) and languages, the situations each is best suited for and how the landscape is changing.

SQL vs NoSQL: Five Main Differences

SQL is the programming language used to interface with relational databases. (Relational databases model data as records in rows and tables with logical links between them). NoSQL is a class of DBMs that are non-relational and generally do not use SQL.

There are five practical differences between SQL and NoSQL:

1. Language
2. Scalability
3. Structure
4. Properties
5. Support and communities

1. Language

SQL has been around for over 40 years, recognisable, documented, and widely used. Safe and versatile, it’s particularly well suited for complex queries. However, SQL restricts the user from working within a predefined tabular schema, and more care must be taken to organise and understand the data before it is used.

The dynamic schemata of NoSQL databases allow the representation of alternative structures, often alongside each other, encouraging greater flexibility. There is less emphasis on planning, greater freedom when adding new attributes or fields, and the possibility of varied syntax across databases. However, NoSQL languages lack SQL’s standard interface, so more complex queries can be difficult to execute.

Though there are many dialects of SQL, all share a common syntax and almost-identical grammar. When querying relational databases, fluency in one language translates to proficiency in most others. On the other hand, there is very little consistency between NoSQL languages, as they concern many unrelated technologies. Many NoSQL databases have a unique data manipulation language constrained by particular structures and capabilities.

2. Scalability

Most SQL databases can be scaled vertically by increasing the processing power of existing hardware. NoSQL databases use a master-slave architecture which scales better horizontally, with additional servers or nodes.

These are helpful generalisations, but it’s important to note:

• SQL databases can be scaled horizontally, though partitioning logic is often the user’s onus and not well supported.
• NoSQL technologies are diverse, and while many rely on the master-slave architecture, options for scaling vertically also exist.
• Savings made using more efficient data structures can overwhelm differences in scalability; the most important is understanding the use case and planning accordingly.

3. Structure

SQL database schemata always represent relational, tabular data, with rules about consistency and integrity. They contain tables with columns (attributes) and rows (records), and keys have constrained logical relationships. In today’s data-driven world, the choice between SQL and NoSQL comes down to your specific use case. At Gorrion, we’ve seen that for systems with structured, tabular data and high consistency requirements, SQL remains a strong choice. But when flexibility and scalability take priority, especially with complex or evolving data, NoSQL solutions offer the adaptability modern businesses need. The key is recognizing that both models can coexist and serve different needs depending on the scale and structure of the data.” – says Bartosz Wrobel, CEO at Gorrion.io

NoSQL databases need not stick to this format but generally fit into one of four broad categories:

• Column-oriented databases transpose row-oriented RDBMSs, allowing efficient storage of high-dimensional data and individual records with varying attributes.
• Key-Value stores are dictionaries which access diverse objects with a key unique to each.
• Document stores hold semi-structured data: objects which contain all of their relevant information and which can be completely different from each other.
• Graph databases add the concept of relationships (direct links between objects) to documents, allowing rapid traversal of greatly connected data sets.

4. Properties

SQL and NoSQL comply with separate rules for resolving transactions at a high level. RDBMSs must exhibit four “ACID” properties:

• Atomicity means all transactions must succeed or fail altogether. They cannot be partially complete, even in the case of system failure.
• Consistency means that the database follows invariants at each step: rules that validate and prevent corruption.
• Isolation prevents concurrent transactions from affecting each other. Transactions must result in the same final state as if they were run sequentially, even if they were run in parallel.
• The durability makes transactions final. Even system failure cannot roll back the effects of a successful transaction.

NoSQL technologies adhere to the “CAP” theorem, which says that in any distributed database, only two of the following properties can be guaranteed at once:

• Consistency: Every request receives the most recent result or an error. (Note this is different than in ACID)
• Availability: Every request has a non-error result, regardless of how recent that result is.
• Partition tolerance: Any delays or losses between nodes will not interrupt the system’s operation.

5. Support and communities

SQL databases represent massive communities, stable codebases, and proven standards. Many examples are posted online, and experts are available to support those new to programming relational data.

NoSQL technologies are being adopted quickly, but communities remain smaller and more fractured. However, many SQL languages are proprietary or associated with large single-vendors, while NoSQL communities benefit from open systems and concerted commitment to onboarding users.

• SQL is available to most major platforms, from operating systems to architectures and programming languages.
• Compatibility varies more widely for NoSQL, and dependencies need to be investigated more carefully.
• Remember that SQL dialects share many properties though they interface with distinct databases.
• Flavours of NoSQL vary far more across their attendant systems, so a comparison can be more useful between multiple non-relational technologies vs SQL generally.

Perhaps the most recognisable SQL dialect is MySQL, an open-source and free RDBMS (though available through proprietary licences as well). Its use is widespread in web applications, and it is known for compatibility, support, and good performance in the average case. MySQL has also made concessions to NoSQL practitioners with features like a JSON data type, the “Document Store,” and support for horizontal scaling.

Generally, NoSQL is preferred for:

1. Graph or hierarchical data
2. Data sets which are both large and mutate significantly,
3. Businesses growing extremely fast but lacking data schemata.
4. This might translate to social networks, online content management, streaming analytics, or mobile applications in terms of use cases.

SQL is more appropriate when the data is:

1. Small
2. Conceptually modelled as tabular
3. In systems where consistency is critical.

Think of small business’ accounting systems, sales databases, or transactional systems like payment processing in e-commerce. SQL is also more appropriate when in doubt, as RDBMSs are better supported and fault-tolerant. SQL is old and sometimes constraining, time-tested, and increasingly considered a universal interface for data analysis. NoSQL databases are new and flexible but lack maturity and require user specialisation. Pragmatically both models are helpful and even growing together.

Ultimately, technology is only valuable when it serves your business, usually with increased ROI. With resources to innovate ad-hoc NoSQL systems from scratch, companies like Google have found that SQL provided additional value and restored it within critical systems.

From migrating hand-coded SQL into compliant and governable ETL tools to managing complex, unstructured data to integrating relational and non-relational systems under one convenient umbrella, Talend provides solutions across data storage paradigms. Centralised and automated data integration software makes source systems easier to manage, whether relational or otherwise. Talend products include tools that even users with little ETL experience can optimise processes. Connectors are available for all major RDBMSs as well as leading NoSQL databases.