SQL Server - Top Features Coming to SQL Server 2017
Microsoft has announced the new SQL Server 2017, in summary, these are the Top Features Coming up:
SQL Server in Linux
SQL Server on Windows today is built on Windows Server failover cluster for high availability and disaster recovery. In this session, we'll show how SQL Server 2017 running on Linux meets the most demanding requirements for high availability using technology that is trusted and familiar for Linux users.
First R, Now Python
Last release Microsoft had implemented R, now SQL has Python to run advanced data analysis, the ability to store and analyze graph data, and other capabilities that help you manage SQL Server for high performance and uptime. It is embedded into T-SQL scripts, where they can be easily deployed to the database as stored procedures and easily called from SQL client applications by stored procedure call," Microsoft said. This functionality and the ability to run R in-database and at scale are only available on Windows Server operating system at this time.
SQL Server Reporting Services (SSRS)
Reporting Services can now be installed outside of the SQL Server setup. You can now upload your Power BI Desktop files to Reporting Services and view reports directly in the web portal.
SQL Server Analysis Services 2017
This release introduces encoding hints, an advanced feature used to optimize processing (data refresh) of large in-memory tabular models. To better understand encoding, see Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services whitepaper to better understand encoding. The encoding process described here applies in CTP 1.3.
Scale Out for SSIS – Distributed Performance Enhancement
The Scale Out feature makes it much easier to run SSIS on multiple machines.
After installing the Scale Out Master and Workers, the package can be distributed to execute on different Workers automatically. If the execution is terminated unexpectedly, the execution is retried automatically. Also, all the executions and Workers can be centrally managed using the Master.
Resumable online index rebuild
Resumable online index rebuild does not require significant log space, which allows you to perform log truncation while the resumable rebuild operation is running. For example, you might need to temporarily free up systems resources in order to execute a high priority task or complete the index rebuild in another miniatous window if the available maintenance windows is too short for a large table.
Graph processing with SQL Server 2017
SQL Server offers graph database capabilities to model many-to-many relationships. A graph database is a collection of nodes (or vertices) and edges (or relationships). A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends).
CREATE TABLE Person (ID INTEGER PRIMARY KEY, name VARCHAR(100)) AS NODE;
CREATE TABLE friends (StartDate date) AS EDGE;
Query language extensions
-- Find friends of John
FROM Person Person1, Friends, Person Person2
AND Person1.Name = 'John';
SQL Automatic tuning
It is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems. Automatic tuning in SQL Server, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems.
CROSS APPLY, and JSON functions
Full support for JSON functions in natively compiled modules, and in check constraints. Support for computed columns in memory-optimized tables, including indexes on computed columns.
CREATE TABLE Product(
ProductID int PRIMARY KEY NONCLUSTERED,
Name nvarchar(400) NOT NULL,
Data nvarchar(4000) CONSTRAINT [Data contains JSON] CHECK (ISJSON(Data)=1),
MadeIn AS CAST(JSON_VALUE(Data, '$.MadeIn') as NVARCHAR(50)) PERSISTED,
Cost AS CAST(JSON_VALUE(Data, '$.ManufacturingCost') as float ),
INDEX [idx_Product_MadeIn] NONCLUSTERED (MadeIn)
New Functions CONCAT_WS, TRANSLATE, TRIM, STRING_AGG