Before SQL Server 2012 release, this product was considered a database management system for small and medium enterprises. Starting with the 2012 release, the database engine is no longer considered for medium-scale enterprises after adding high-end data-center management capabilities. In November 2019, SQL Server 2019 Big Data Clusters were introduced, giving the ability for users to build a Big Data ecosystem.
This article will briefly mention nine features added starting SQL Server 2008 that make SQL Server more than a traditional database management system.
Data Compression (SQL Server 2008+)
This feature was added in SQL Server 2008 to be applied to tables and indexes. There are two types of compression:
- Row compression: Alter the physical storage format of the data based on its data type.
- Page Compression: It applies the “row compression”, plus two compression operations (Prefix and dictionary) which are based on the data syntax.
Using the data compression feature helps reduce database size and the I/O workloads since the number of data pages needed to store data are decreased, which means that the executed tasks and queries read fewer pages from the disk. This feature’s “side effect” requires more CPU resources to decompress data before being consumed.
Considering the Columnstore tables and indexes introduced in SQL Server 2012, data compression is always applied.
Columnstore Index (SQL Server 2012+)
Columnstore indexes are much like the column-based NoSQL database. It is designed to store a massive amount of data, especially fact tables, to be used in analytical operations. Include more pleasurable having parhaat online pelit. This feature was improved in SQL Server 2016, where they increased querying data performance ten times than the rowstore tables.
Besides, since data compression is always applied to columnstore indexes (as we mentioned before), the data is stored within a fewer number of pages than the uncompressed data.
Memory-Optimized tables (SQL Server 2014+)
Also known as In-Memory OLTP tables. Similar to standard tables, these tables are stored within the hard drive (durable disk), but also they have a copy within the active memory (hidden copy). These tables are optimized to perform faster transactions.
Instead of using table locks while running transactions, these tables use row versioning to keep the original data till transactions commitment. Besides, these tables are not fully logged.
JSON Support (SQL Server 2016+)
JavaScript Object Notation (JSON) is an open universal data format that uses human-readable text to store data objects in key/value pairs. Recently, this notation became widely used to exchange data using API’s to store data within NoSQL databases.
In SQL Server 2016, JSON becomes supported, letting the developers combine NoSQL and relational concepts by storing documents formatted as JSON text within relational tables. Four main JSON functionalities was added allowing developers to:
- Parse JSON text and read or modify values
- Transform arrays of JSON objects into table format
- Run any Transact-SQL query on the converted JSON objects
- Format the results of Transact-SQL queries in JSON format
Polybase (SQL Server 2016+)
Polybase is a feature introduced in SQL Server 2016 that allows querying data from external data sources such as Hadoop using T-SQL. Queries are executed without the need to create Linked Servers objects which have lower performance. In SQL Server 2019, new external data sources were supported, such as Oracle, Teradata, MongoDB databases.
Machine Learning services (SQL Server 2016+)
When it was firstly introduced in SQL Server 2016, it was called R services. In the 2017 release, the name was changed to Machine Learning services since Python became supported.
The Machine Learning services allow running Python and R scripts over the data to perform analytics and machine learning algorithms using the popular packages. One of the main advantages is that the script execution is performed within the database engine without moving data outside SQL Server or over the network.
Graph Database (SQL Server 2017+)
This feature was introduced in SQL Server 2017, allowing users to create graphs similar to the NoSQL graph databases (node and edges). Even if popular NoSQL graph databases (such as Neo4J) are recommended to store graphs, but in some cases, it is useful to have the graph data stored within the SQL database engine to facilitate the data integration process.
SSIS Scale-out (SQL Server 2017+)
To perform distributed data integration operations, SQL Server Integration Services (SSIS) scale-out was introduced in SQL server 2017. It allows the execution of the SSIS packages across multiple computers, while in the previous versions implementing a distributed approach was very complicated.
Containerization (SQL Server 2017+)
Starting SQL Server 2017, a Docker image for SQL Server was provided by Microsoft, allowing users to install SQL Server a wider range of operating systems.
This feature was the main building block that allowed building Big Data Cluster introduced in SQL Server 2019 since it gives the ability to connect multiple SQL Server nodes (Docker containers) using Kubernetes (container orchestrator).
출처 : https://medium.com/munchy-bytes/nine-features-that-made-sql-server-more-than-a-traditional-dbms-342baa60eaed