SQL Server 2017 New Features - 23 July 2017 15:56

Contents

• 0. Initial setup
• 0.1 Release notes
• 0.2 Demo database setup including WorldWideImporters
• 1. Installation
• 1.1. Reporting Services is now a seperate installation
• 1.2. R Services has been renamed to Machine Learning Services (In-Database)
• 1.3. Standalone R Server has been renamed to Machine Learning Server (Standalone)
• 1.4. Tempdb can be increased to up to 256GB
• 1.5. Tempdb file sizes greater than 1GB result in a warning if Instant File Initialization (IFI) is not enabled
• 1.6. Download is a tiny binary which be be used to install directly or download media (RC2)
• 2. Database Engine
• 2.1 Database Engine Feature Enhancements
• 2.1.1 Columnstore Indexes
• 2.1.2 In-Memory OLTP
• 2.1.3 Query Store
• 2.1.4 Temporal Tables
• 2.2 Transact-SQL Enhancements
• 2.2.1 SELECT INTO ON FILEGROUP
• 2.2.2 New Functions Concat_WS,TRANSLATE,TRIM
• 2.2.3 WITHIN GROUP for STRING_AGG function
• 2.2.4 Resumable online index builds
• 2.2.5 Database Scoped Configuration new IDENTITY_CACHE option
• 2.2.6 Bulk access with CSV format files
• 2.2.7 Bulk access from Azure Blob Storage
• 2.3 System View Enhancements
• 2.3.1 New DMF sys.dm_db_log_stats
• 2.3.2 Enhanced DMV sys.dm_os_sys_info
• 2.3.3 Enhanced DMV sys.dm_db_file_space_usage
• 2.3.4 New DMV sys.dm_tran_version_store_space_usage
• 2.3.5 New DMF sys.dm_db_log_info
• 2.3.6 New DMV sys.dm_db_stats_histogram
• 2.3.7 New DMV sys.dm_os_host_info
• 2.3.8 New DMV sys.sys.dm_db_tuning_recommendations
• 2.4 Security Enhancements
• 2.4.1 CLR
• 2.4.2 Database Scoped Credential enhancements
• 2.4.3 Database roles created with R Services for managing permissions associated with packages.
• 2.5 High Availability Enhancements
• 2.5.1 Clusterless Availability Groups
• 2.5.2 Minimum Replica Commit
• 2.6 Performance
• 2.6.1 Backup performance for small databases on large memory servers
• 2.6.2 Indirect Checkpoint performance improvements
• 2.7 Query Optimization
• 2.7.1 Improvements thresholds for incremental statistics building with database compatability level 140
• 2.7.2 Statistics Usage in Actual Query Plans
• 2.7.3 Interleaved Execution for Multi-Statement Table Valued Functions (MSTVfs)
• 2.7.4 Interleaved Execution via Adaptive Joins
• 2.8 Collation
• 2.8.1 2 new japanese collation families
• 2.8.2 Option _VSS for japense collations
• 2.9 Graph Processing
• 2.10 Python scripts
• 2.11 Tools Enhancements
• 2.11.1 DTA (Database Engine Tuning Advisor)
• 2.11 Tools Enhancements
• 2.11 Linux differences

0. Initial setup

0.1 Release notes

Release notes are:

• CTP 1.0 - Announcing SQL Server on Linux public preview, first preview of next release of SQL Server
• CTP 1.1 - SQL Server 2016 Community Technology Preview 2.1 is available
• CTP 1.2 - SQL Server next version Community Technology Preview 1.1 now available
• CTP 1.3 - SQL Server next version CTP 1.3 now available
• CTP 1.4 - SQL Server next version CTP 1.4 now available
• CTP 2.0 - SQL Server 2017 Community Technology Preview 2.0 now available
• CTP 2.1 - SQL Server 2017 CTP 2.1 now available
• RC1 - First release candidate of SQL Server 2017 now available
• RC2 - SQL Server 2017 Release Candidate 2 (RC2) is now available

0.2 Demo database setup

Demo database justdave setup

MD C:\DATA

CREATE DATABASE [justdave];

ALTER DATABASE [justdave] ADD FILEGROUP [justdave1] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE [justdave] 
ADD FILE (name='justdave1_1', filename='c:\data\justdave1_1') 
TO FILEGROUP [justdave1];

USE [justdave];

Also restore the WorldWideImpporters Database from Wide World Importers sample database v1.0

Download WideWorldImporters-Full.bak then

RESTORE DATABASE [WideWorldImporters] 
FROM DISK = 'C:\x\WideWorldImporters-Full.bak'
WITH MOVE 'WWI_Primary' TO 'C:\DATA\WideWorldImporters.mdf',
MOVE 'WWI_UserData' TO 'C:\DATA\WideWorldImporters_UserData.ndf',
MOVE 'WWI_Log' TO 'C:\DATA\WideWorldImporters.ldf',
MOVE 'WWI_InMemory_Data_1' TO 'C:\DATA\WideWorldImporters_InMemory_Data_1',REPLACE;



NOTE: The database is automatically upgraded to Compatability Level 130 (2016) NOT Compatability Level 140 (2017)!