Sql Server 2016 New Features

quick link

Query Store

Query Store: (query Optimizer )
Query Store configured at database level.
query store best practice
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);
ALTER DATABASE [QueryStoreDB] SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));

USE [QueryStoreDB];
GO
SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, 
    max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;


sys.sp_xtp_control_query_exec_stats added to sys.query_store_planto

The Query Store feature maintains a history of query execution plans 
with their performance data, and quickly identifies queries that 
have gotten slower recently, allowing administrators or developers 
to force the use of an older, better plan if needed. 

Query Store manages its metadata in the local database, but it is disabled by default
-- a best practice is to enable it on the model database.
ALTER DATABASE MODEL SET QUERY_STORE=ON

ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON 
( OPERATION_MODE = READ_WRITE , 
  CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 5 ) , 
  DATA_FLUSH_INTERVAL_SECONDS = 2000 , 
  MAX_STORAGE_SIZE_MB = 10 , 
  INTERVAL_LENGTH_MINUTES = 10 
);

Related views:
query_store_runtime_stats
query_store_runtime_stats_interval
query_store_plan
query_store_query
query_store_query_text

-- Finding the poorest performing queries over the last hour 

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id, 
              qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime, 
              rs.last_execution_time 
FROM sys.query_store_query_text AS qt 
JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id 
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id 
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id 
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE()) 
ORDER BY rs.avg_duration DESC;

-- how to unforce a plan ( against parameter sniffing )
unforce a plan by using either the Query Store interface in SSMS 
or the sp_query_store_unforce_plan stored procedure.

Polybase -- Direct Query To Hadoop

turn on TRACEFLAG: DBCC TRACEON(4631,-1);
Polybase ( Direct Query to Hadoop  )
INSERT INTO EXTERNAL TABLE SELECT FROM TABLE
ALTER EXTERNAL DATA SOURCE

JSON Support

The JSON type can be used for column and variable data types.
JSON Support (Java Script Object Notation (JSON)
SELECT * FROM Table FOR JSON AUTO;
SELECT * FROM Table FOR JSON AUTO, INCLUDE_NULL_VALUES;
SELECT * FROM Table FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;
SELECT * FROM Table FOR JSON PATH;
SELECT * FROM Table FOR JSON PATH, ROOT('info');
SELECT * FROM Table FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Select IsJSON('{"name":"John","surname":"Doe","age":45}')
Select * from OpenJSON('{"name":"John","surname":"Doe","age":45}.)

ISJSON -Tests whether a string contains valid JSON.
JSON_VALUE .Extracts the value from JSON.
JSON_QUERY .Extracts subquery from JSON.
OPEN_JSON -Table value function that parses JSON text and returns rowsetview of JSON.

Stretch Database into Azure

Stretch Database into Azure: (  Some data moved to Azure SQL Database in the cloud )
EXEC sp_configure 'remote data archive', '1'; 
GO 
RECONFIGURE; 
GO

USE [AdventureWorks2014];
GO
 
ALTER TABLE [StretchTest] ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE = ON );
GO

-- backup to azure
EXEC sys.sp_reauthorize_remote_data_archive @azure_username, @azure_password;
GO

In-Memory OLTP Enhancements

In-Memory OLTP Enhancements ( in-memory tables )
Supporting foreign keys check and unique constraints and parallelism.

-- Memory-optimized table
SQL Server drops memory-optimized tables whre the service is restarted, and all data is lost

Enable Live Query Statistics For a Session

SET STATISTICS XML ON

Columnstore Index

column store indexes, which are commonly used in data warehouse workloads
One clustered columnstore index, or one nonclustered columnstore index can be added to one table

Temporal Database Support

Temporal Database Support
select * from department for system_time as of '2016.05.28';
select * from department for system_time all
select * from department for system_time contained in

SELECT * FROM Employee 
    FOR SYSTEM_TIME  
        BETWEEN '2014-01-01 00:00:00.0000000' AND '2015-01-01 00:00:00.0000000' 
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

SYSTEM_VERSIONING ON|OFF

CREATE TABLE dbo.Employee 
(  
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED 
  , [Name] nvarchar(100) NOT NULL
  , [Position] varchar(100) NOT NULL 
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) NOT NULL
  , [AnnualSalary] decimal (10,2) NOT NULL
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

CREATE TABLE dbo.TestTemporal
	(ID int primary key
	,A int
	,B int
	,C AS A * B
	,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
	,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
	,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON);

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON
 (HISTORY_TABLE=dbo.TestTemporal_History,DATA_CONSISTENCY_CHECK=[ON/OFF])
);

Columnstore (Index )

CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactResellerSales
ON dbo.FactResellerSales
(ProductKey, UnitPrice, CustomerPONumber, OrderDate);

-- Regular Table, COLUMNSTORE Index

CREATE TABLE SimpleTable(  
    ProductKey [int] NOT NULL,   
    OrderDateKey [int] NOT NULL,   
    DueDateKey [int] NOT NULL,   
    ShipDateKey [int] NOT NULL);  
GO  
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON SimpleTable;  
GO 

CREATE TABLE SimpleTable (  
    ProductKey [int] NOT NULL,   
    OrderDateKey [int] NOT NULL,   
    DueDateKey [int] NOT NULL,   
    ShipDateKey [int] NOT NULL);  
GO  
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);  
GO  
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON SimpleTable  
WITH (DROP_EXISTING = ON);  
GO  

Always Encrypted ( Client Software )

To protect highly sensitive data by encrypting specific columns.
Randomized encryption: Credit Card Numbers, SOcial Security Numbers
Deterministic encryption: May need to index the column, like Birthday, Regions
Always Encryted stores encryption keys not on the server, but on the client side
Always Encrypted ( Client Software )
( client connection string: SqlCommandColumnEncryptionSetting: Disabled|Enabled|ResultSet )
column-level encryption 
encryption at rest
encryption in transit
--> Create User ... ALLOW_ENCRYPTED_VALUE_MODIFICATIONS
eg1:
USE [Samples]
GO
CREATE COLUMN MASTER KEY MyKey
WITH
(
     KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
     KEY_PATH = N'CurrentUser/My/DE3A770F25EBD6071305B77FB198D1AE434E6014'
);
GO

eg2:
USE [Samples] 
GO 
CREATE COLUMN ENCRYPTION KEY [MyColumnKey] WITH VALUES 
( COLUMN MASTER KEY DEFINITION = [MyKey], ALGORITHM = 'RSA_OAEP', 
  ENCRYPTED_VALUE = 0x016E008000630075007200720065006E00740075007300650072002F006D0079002F0064006500330061003700370030006600320035006500620064003600300037003100330030003500620037003700660062003100390038006400310061006500340033003400650036003000310034004D74119935C902E59F57A96C3E6F770826D247135FFFA759B5B013DF4DAF7CFB760A5864DD8381B91924D067BE4F574B50DE7F0D53F278E1C003B5D192865B808C1590224F4A4BB463255101C36D3089F46609B376D7B00FA9F9CEAF715398EECAB790AC6EC8BD18C17B3EB992CAE08FEA6A2F5A2BDDA4F5A700744E45861F993A3C488127E5897B30892DD2734DD5D84F096882A393D5877C5A20E392888FE0357F46DB578AEB4C677CFFCE2281276C4D12F3E5AC3BCCC09B78BB0E522D86F9B2CF989F14695B7CB95A478194ECBD175B5C7C1687B7589FD9145B2782CB0BBAB6F7F5B0AC7F8C256EB0D3D87ABAE4F73137FA4AFA387B791B54AC503B53271D ); 
GO

COumn Name: SSN, Column Master Ket Definition: MyKey
eg3:
USE [Samples] 
GO 
CREATE COLUMN ENCRYPTION KEY [MyColumnKey] WITH VALUES 
( COLUMN MASTER KEY DEFINITION = [MyKey], ALGORITHM = 'RSA_OAEP', 
   ENCRYPTED_VALUE = 0x016E008000630075007200720065006E00740075007300650072002F006D0079002F0064006500330061003700370030006600320035006500620064003600300037003100330030003500620037003700660062003100390038006400310061006500340033003400650036003000310034004D74119935C902E59F57A96C3E6F770826D247135FFFA759B5B013DF4DAF7CFB760A5864DD8381B91924D067BE4F574B50DE7F0D53F278E1C003B5D192865B808C1590224F4A4BB463255101C36D3089F46609B376D7B00FA9F9CEAF715398EECAB790AC6EC8BD18C17B3EB992CAE08FEA6A2F5A2BDDA4F5A700744E45861F993A3C488127E5897B30892DD2734DD5D84F096882A393D5877C5A20E392888FE0357F46DB578AEB4C677CFFCE2281276C4D12F3E5AC3BCCC09B78BB0E522D86F9B2CF989F14695B7CB95A478194ECBD175B5C7C1687B7589FD9145B2782CB0BBAB6F7F5B0AC7F8C256EB0D3D87ABAE4F73137FA4AFA387B791B54AC503B53271D ); GO

CREATE TABLE [dbo].[Customers]( 
  [CustomerId] [int] IDENTITY(1,1), 
  [TaxId] [varchar](11) COLLATE Latin1_General_BIN2 
    ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
                    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
                    COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL, 
  [FirstName]  [nvarchar](50) NULL, 
  [LastName]   [nvarchar](50) NULL, 
  [MiddleName] [nvarchar](50) NULL, 
  [Address1]   [nvarchar](50) NULL, 
  [Address2]   [nvarchar](50) NULL, 
  [Address3]   [nvarchar](50) NULL, 
  [City]       [nvarchar](50) NULL, 
  [PostalCode] [nvarchar](10) NULL, 
  [State] [char](2) NULL, 
  [BirthDate] [date] 
    ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, 
                    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',    
                    COLUMN_ENCRYPTION_KEY = MyColumnKey) NOT NULL 
   PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] ); 
GO

Row-Level Security

Row-Level Security ( RLS )
restricts which users can view what data in a table, based on a function
--> SESSION_CONTEXT, CONTEXT_INFO, reset by sp_reset_connection
--> sp_set_session_context @key=N'key', @value=N'value'[,@read_only={1,0}]
    SESSION_CONTEXT ( N'key' )
--> SCHEMABINDING = OFF
-- old: CREATE FUNCTION dbo.fn_Orders(@SalesRep AS sysname) 
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN SELECT 1 AS fn_Orders_result WHERE @SalesRep = USER_NAME(); 
GO
-- one username with different userid:
CREATE FUNCTION dbo.fn_Orders(@SalesRep AS int) 
RETURNS TABLE WITH SCHEMABINDING AS 
RETURN SELECT 1 AS fn_Orders_result WHERE @SalesRep = CONVERT(SESSION_CONTEXT(N'UserId') AS int); 
GO

CREATE PROCEDURE GetOrder 
   @OrderId int, 
   @UserId int 
AS 
EXEC sp_set_session_context @key=N'UserId', @value=@UserId; 

SELECT * FROM Orders WHERE OrderId = @OrderId; 
GO

Dynamic Data Masking

Four Types: 
1): Email: partially obfuscate email addresses. This may be used to identify people, without revealing the full email address.
2): Phone number: partially obfuscate phone numbers. May be used to confirm which phone number is held in the database, without revealing the full phone number.
3): Partial: can be customized to specific requirements.
4): Random: can be customized to specific requirements.

Dynamic Data Masking
CREATE TABLE [dbo].[Customer]( 
   [CustomerId] [int] IDENTITY(1,1) NOT NULL, 
   [TaxId] [varchar](11) MASKED WITH (FUNCTION = 'default()'), 
   [FirstName] [nvarchar](50) MASKED WITH (FUNCTION = 'partial(3, "xyz", 1)') NULL, 
   [LastName] [nvarchar](50) NULL, 
PRIMARY KEY CLUSTERED 
( [CustomerId] ASC) 
) ON [PRIMARY]; 
GO

ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] ADD MASKED WITH (FUNCTION = 'default()')

ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] DROP MASKED;

-- real data is still stored in the database, mask only when read out data
GRANT UNMASK TO MyUser; 
REVOKE UNMASK TO MyUser;

AlwaysON Enhancement

AlwaysON Enhancement:
Up to 3 Synchronous replicas for auto failover across domains
Round-robin load balancing of replicas
DTC & SSIS SUpport
Automatic failover based on database health

CREATE AVAILABLITY GROUP [2016DEMO] WITH DTC_SUPPORT=PER_DB

Trace Flag

Allocations And Auto Growth ( Trace Flag )
TF1117 - Enabled By Default
TF1118 - Enabled By Default

Trace flag 1117
Trace flag (TF) 1117 is related strictly to file groups and how data files grow within them. 
A file group is a logical container for one or more data files within a database. 
TF 1117 forces all data files in the same file group to grow at the same rate, 
which prevents one file from growing more than others, leading to the hotspot 
issue described earlier in this chapter. Enabling this trace flag in earlier 
versions of SQL Server is a minor tradeoff in performance.

Trace flag 1118
Administrators use trace flag 1118 to change page allocation from a GAM page. 
When you enable TF 1118, SQL Server allocates eight pages, 
or one extent, at a time to create a dedicated (or uniform) extent, 
in contrast to the default behavior to allocate a single page from a mixed extent.

Advanced Analytics - R:

Advanced Analytics - R:
USE [master] 
GO 
CREATE USER [] FOR LOGIN [] WITH DEFAULT_SCHEMA=[db_rrerole]; 
ALTER ROLE [db_rrerole] ADD MEMBER [];
GO

Exec sp_configure'external scripts enabled', 1; 
Reconfigure;
exec sp_execute_external_script @language='R', @script=-- R code -- 

EXECUTE sp_execute_external_script
@language = N'R'
, @script = N'OutputDataSet<-InputDataSet'
, @input_data_1 = N'SELECT 1 as Col'
WITH RESULT SETS ((col intnot null));

EXEC sp_execute_external_script
    @language = N'R' , 
    @script = N' library(e1071); 
                 irismodel<-naiveBayes(iris_data[,1:4], iris_data[,5]); 
                 trained_model<-data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));', 
    @input_data_1 = N'select"Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species" from iris_data' , 
    @input_data_1_name = N'iris_data' , 
    @output_data_1_name = N'trained_model' WITH RESULT SETS ((model varbinary(max)));

Others

DBCC CheckTable, CheckDB, CheckFileGroup ... ... MAXDOP

Online Alter COlumn

EXECUTE AS CALLER

execute as user='MyUser'

Multiple TempDBs upon Install

TempDB: Multiple temp datafiles

SET SHOWPLAN_XML is set to ON