SQL Server 2019 New Business Intelligence Features

Since I am a BI expert this article is limited to BI related enhancements in SQL server 2019, Please download this white paper if you are looking for complete set of new features.

https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html

1. Data Virtualization and enhanced Poly base:

With evolution of IoT and social media applications data world has expanded from relational databases to NoSQL, Graph, Hadoop and much more. Although Polybase was introduced in earlier versions of SQL server but it allowed connecting to only Hadoop based systems (Cloudera, Spark, Hortonworks) but now with SQL Server 2019 you can also connect to more relational and no relational sources such as Oracle, Teradata, IBMDB2, Sap Hana, MongoDB, Cosmos DB, Cassandra and expose the data as external tables in same SQL Server instance. This enables users to combine data from all these sources by writing SQL joins etc.

SQL Server Polybase will be smart enough to push compute operations like aggregates, predicates and Homogeneous joins back to original source system automatically. SQL 2019 also supports Scale out compute and caching to boost performance, having this virtual layer called data hub over all your data also makes providing access to users simple by leveraging roles and group-based security which is native to SQL server instance.

2. SQL Server Big Data / Data Lake/ AI:

SQL Server 2019 will have built in SPARK along with R & python which was part of SQL server 2017 which will allow big data engineers to use SPARK instead of SQL to do data transformations and build train deploy machine learning models. There is new admin portal available to build/deploy SPARK queries and jobs.

3. SQL data discovery and classification:

Just like azure SQL DB SQL server 2019 will have built in tool for data discovery and classification which will help identify / classify sensitive data and generate reports to meet regulatory requirements such as GDPR.

4. Batch Mode:

SQL Server 2019 will have Batch mode on row store which allows query operators to process data more efficiently by working on batch of rows instead of one row at a time, SQL server 2017 had this mode available only for tables with column store.

5. Approximate COUNT DISTINCT:

SQL Server 2019 will have new function APPROX_COUNT_DISTINCT to process distinct count queries over very large datasets much faster and uses less memory than COUNT(DISTINCT) function but count may or may not be 100 percent accurate.