SQL Server 2016 CTP New Features - 3rd March 21:53

0. Contents

1. 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];

1. Installation

1.1. Additional Installation Requirements above SQL Server 2014

Manuals are at Books Online for SQL Server 2016

SSMS needs .NET Framework 4.6

SQL Server 2016 CTP2.0 needs Oracle JRE Update 51 (64-bit) or higher

Download from Oracle JRE download

Choose Java top left picture then "Java SE Development Kit 8u45" Accept the License and download "Windows x64"

Click on Run the on the Taskbar bring the installer to the front and just keep clicking Next!

In CTP 2.3 if Polybase is being installed the Server Collation must be either Latin1_General_100_AS_KS_WS or SQL_Latin1_General_CP1_CI_AS

This can be selected on the Server Configuration Screen during installation

Sharepoint (even 2013) wants Windows Collation Latin1_General_CI_AS_KS_WS so this was chosen

SQL Server 2016 CTP 2.3 collation with Polybase

2. Database Engine

2.1 Database Engine Feature Enhancements

2.1.1 Columnstore Indexes

2.1.1.1 Many enhancements (CTP 2.0)

2.1.1.2 A read-only nonclustered columnstore index is updateable after upgrade (CTP 3.0)

After the upgrade process read-only nonclustered columnstore index are writeable

2.1.1.3 Performance improvements for analytics queries on columnstore indexes (CTP 3.0)

Additional logical operators can run in batch mode

IF OBJECT_ID('dbo.Numbers1', 'U') IS NOT NULL
 DROP TABLE dbo.Numbers1;

CREATE TABLE dbo.Numbers1 (
  NumberID int NOT NULL,
  Name varchar(10)
);

CREATE CLUSTERED COLUMNSTORE INDEX ix_NumberID1 ON dbo.Numbers1;

-- To get parallel execution on 2014 run on VM with >1 cpu
-- sp_configure 'show advanced options',1;
-- reconfigure;
-- sp_configure 'max degree of parallelism',2;
-- sp_configure 'cost threshold for parallelism',0;
-- reconfigure;
-- Increase TOP to 1000000
--
WITH
  Pass0 as (select 1 as C union all select 1), --2 rows
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
  Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
  Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
  Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT dbo.Numbers1 (NumberID)
 select TOP(10000) Number
  from Tally;

ALTER INDEX ix_NumberID1 ON dbo.Numbers1 REORGANIZE;

-- Check row groups
SELECT b.name,a.partition_number,a.row_group_id,a.state_description,a.total_rows,a.size_in_bytes
FROM sys.column_store_row_groups a
INNER JOIN sys.objects b
ON a.object_id = b.object_id
WHERE b.object_id IN (OBJECT_ID('dbo.Numbers1', 'U'))
ORDER BY b.name,a.partition_number,a.row_group_id;

Additional operators can run in batch mode - WIP

Aggregate computation can be pushed down to be done during the scan node of the query plan

The following operations support pushing the aggregate calculation down to be done during the SCAN node of a query plan

Limitations are:

The aggregation is done on compressed/encoded data in cache-friendly execution and by leveraging SIMD

String predicates can be pushed down in the SCAN node during a query plan

The primary/secondary dictionary created for column compression is used for predicate processing

The predicate is computed against just the dictionary entries rather than each row

If the dictionary entry passes the predicate all rows referring to the dictionary entry are automatically qualified

This results in potential performance improvements:

2.1.1.4 DMVs and XEvents have supportability improvements

New DMVs

Updated DMVs

Need to add XEvents - FIXME

2.1.2 In-Memory OLTP

2.1.2.1 Transact-SQL Improvements

2.1.2.1.1 Query Surface Area in Native Modules (CTP 2.0/CTP 3.0/CTP 3.1/CTP 3.2)

Newly supported constructs are

2.1.2.1.2 Altering Natively Compiled Stored Procedures (CTP 2.0)
IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE Person 
(
PersonID INT NOT NULL,
PersonName NVARCHAR(80),
PersonQuality NVARCHAR(50),
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

INSERT into dbo.Person (PersonID) Values(1);

IF OBJECT_ID('dbo.PersonUpdateQuality', 'P') IS NOT NULL
 DROP PROCEDURE dbo.PersonUpdateQuality;

CREATE PROCEDURE dbo.PersonUpdateQuality(@PersonID INTEGER)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS SELF
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.Person Set PersonQuality='OK' 
     WHERE PersonID=@PersonID;
END;

SELECT * FROM dbo.Person;
EXECUTE dbo.PersonUpdateQuality 1;
SELECT * FROM dbo.Person;

ALTER PROCEDURE dbo.PersonUpdateQuality(@PersonID INTEGER,@PersonQuality NVARCHAR(50))
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS SELF
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.Person Set PersonQuality=@PersonQuality 
     WHERE PersonID=@PersonID;
END;

SELECT * FROM dbo.Person;
EXECUTE dbo.PersonUpdateQuality 1,'Excellent';
SELECT * FROM dbo.Person;

-- CTP 2.2 add EXECUTE AS CALLER
CREATE PROCEDURE dbo.PersonUpdateQuality(@PersonID INTEGER)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS CALLER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.Person Set PersonQuality='OK' 
     WHERE PersonID=@PersonID;
END

IF OBJECT_ID('dbo.PersonUpdateQuality', 'P') IS NOT NULL
 DROP PROCEDURE dbo.PersonUpdateQuality;
2.1.2.1.2 Altering Memory-Optimized Tables (CTP 2.0)
IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE dbo.Person 
(
PersonID INT NOT NULL,
PersonName NVARCHAR(80),
PersonQuality NVARCHAR(50) NOT NULL,
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);
 
INSERT into dbo.Person (PersonID,PersonQuality) Values(1,'First Person');
INSERT into dbo.Person (PersonID,PersonQuality) Values(2,'Second Person');

-- Alter Memory Optimized Table - NEW
ALTER TABLE dbo.Person
 ADD PersonComments NVARCHAR(200) NOT NULL CONSTRAINT PersonOK DEFAULT 'OK' WITH VALUES;

ALTER TABLE dbo.Person
 ADD CONSTRAINT PK_PersonID PRIMARY KEY NONCLUSTERED (PersonID);

ALTER TABLE dbo.Person
 ADD INDEX Quality (PersonQuality);

ALTER TABLE dbo.Person
 DROP INDEX Quality;

ALTER TABLE dbo.Person
 DROP CONSTRAINT PersonOK;

ALTER TABLE dbo.Person
 DROP COLUMN PersonComments;

ALTER TABLE dbo.Person
 ALTER INDEX ix_PersonID
 REBUILD WITH (BUCKET_COUNT=5000);

2.1.2.1.3 Full support for all Collation and Unicode Support (CTP 2.0)

In CTP 2.0 In-Memory OLTP now fully supports collations.

2.1.2.1.4 Scalar User-Defined Functions (CTP 2.0)
-- All syntax needed
-- Msg 10796, Level 15, State 2, Procedure fn_securitypredicate, Line 27
-- The SCHEMABINDING option is supported only for natively compiled modules, and is required for those modules.
-- Msg 10783, Level 15, State 2, Procedure fn_securitypredicate, Line 19
-- The body of a natively compiled module must be an ATOMIC block.
-- Msg 10784, Level 15, State 1, Procedure fn_securitypredicate, Line 18
-- The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'transaction isolation level'.
-- Msg 10784, Level 15, State 1, Procedure fn_securitypredicate, Line 18
-- The WITH clause of BEGIN ATOMIC statement must specify a value for the option 'language'.

IF OBJECT_ID('dbo.fn_AddOne', 'FN') IS NOT NULL
 DROP FUNCTION dbo.fn_AddOne;

CREATE FUNCTION dbo.fn_AddOne(@Number INTEGER)
    RETURNS INTEGER
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS SELF
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    RETURN @Number+1;
END;

-- CTP 2.2 add EXECUTE AS CALLER
IF OBJECT_ID('dbo.fn_AddOne', 'FN') IS NOT NULL
 DROP FUNCTION dbo.fn_AddOne;

CREATE FUNCTION dbo.fn_AddOne(@Number INTEGER)
    RETURNS INTEGER
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS CALLER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    RETURN @Number+1;
END;
2.1.2.1.5 Constraints (CTP 3.0)

Constraints on in-memory OLTP tables

IF OBJECT_ID('dbo.Person', 'U') IS NOT NULL
 DROP TABLE dbo.Person;

CREATE TABLE Person 
(
PersonID INT NOT NULL PRIMARY KEY NONCLUSTERED,
PersonName NVARCHAR(80),
PersonAge INT CONSTRAINT cc_Page CHECK (PersonAge>0),
PersonEmployeeID INT NOT NULL,
PersonQuality NVARCHAR(50),
INDEX ix_PersonID NONCLUSTERED HASH (PersonId) WITH (BUCKET_COUNT=2000),
CONSTRAINT uc_PersonEmployeeID UNIQUE (PersonEmployeeID)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL
 DROP TABLE dbo.Orders;

CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
SalesPerson INT FOREIGN KEY (SalesPerson) REFERENCES dbo.Person(PersonID), 
UnitsSold INT
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);
2.1.2.1.6 Triggers (CTP 3.0)

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders; CREATE TABLE Orders ( OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED, Department VARCHAR(20), UnitsSold INT ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY); IF OBJECT_ID('dbo.TotalOrders', 'U') IS NOT NULL DROP TABLE dbo.TotalOrders; CREATE TABLE TotalOrders ( Department VARCHAR(20) PRIMARY KEY NONCLUSTERED, TotalOrders INT NOT NULL, LastAction CHAR(1) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY); INSERT INTO dbo.TotalOrders (Department,TotalOrders) VALUES ('Sales',0); IF OBJECT_ID('dbo.OrderInsert', 'TR') IS NOT NULL DROP TRIGGER dbo.OrderInsert; CREATE TRIGGER OrderInsert ON dbo.Orders WITH NATIVE_COMPILATION,SCHEMABINDING AFTER INSERT AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') UPDATE dbo.TotalOrders SET TotalOrders=TotalOrders+1, LastAction='I' WHERE Department='Sales'; END; SELECT * FROM dbo.Orders; SELECT * FROM dbo.TotalOrders; INSERT INTO dbo.Orders(OrderID,Department,UnitsSold) VALUES(1,'Sales',10); INSERT INTO dbo.Orders(OrderID,Department,UnitsSold) VALUES(2,'Sales',20); SELECT * FROM dbo.Orders; SELECT * FROM dbo.TotalOrders; IF OBJECT_ID('dbo.OrderDelete', 'TR') IS NOT NULL DROP TRIGGER dbo.OrderDelete; CREATE TRIGGER OrderDelete ON dbo.Orders WITH NATIVE_COMPILATION,SCHEMABINDING AFTER DELETE AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') UPDATE dbo.TotalOrders SET TotalOrders=TotalOrders-1, LastAction='D' WHERE Department='Sales'; END; SELECT * FROM dbo.Orders; SELECT * FROM dbo.TotalOrders; DELETE FROM dbo.Orders where OrderID=1; SELECT * FROM dbo.Orders; SELECT * FROM dbo.TotalOrders; IF OBJECT_ID('dbo.OrderUpdate', 'TR') IS NOT NULL DROP TRIGGER dbo.OrderUpdate; CREATE TRIGGER OrderUpdate ON dbo.Orders WITH NATIVE_COMPILATION,SCHEMABINDING AFTER UPDATE AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') UPDATE dbo.TotalOrders SET LastAction='U' WHERE Department='Sales'; END; SELECT * FROM dbo.Orders; SELECT * FROM dbo.TotalOrders; UPDATE dbo.Orders SET UnitsSold = 5 WHERE OrderID=2; SELECT * FROM dbo.Orders; SELECT * FROM dbo.TotalOrders;

2.1.2.1.7 NULLable index key columns,LOB types,UNIQUE indexes (CTP 3.1)

In CTP 3.1