Two free SQL Server books from Microsoft Press

I noticed that the Microsoft Virtual Academy has a bunch of SQL Server books available that you might enjoy. These books are free, the data science one come in pdf format, while the introducing SQL

Data Science with Microsoft SQL Server 2016
Buck Woody, Danielle Dean, Debraj GuhaThakurta, Gagan Bansal, Matt Conners, Wee-Hyong Tok


Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud
Stacia Varga, Denny Cherry, and Joseph D’Antoni





There is also a Power BI book available, if you interested in that instead

Here is what is in the 2 books I mentioned


Data Science with Microsoft SQL Server 2016

Chapter 1: Using this book 1
For the data science or R professional  1
Solution example: customer churn 3
Solution example: predictive maintenance
and the Internet of Things 4
Solution example: forecasting  5
For those new to R and data science  7
Step one: the math  8
Step two: SQL Server and Transact-SQL 11
Step three: the R programming language
and environment 14

Chapter 2: Microsoft SQL Server R
Services 16
The advantages of R on SQL Server  16
A brief overview of the SQL Server R
Services architecture  21
SQL Server R Services 21
Preparing to use SQL Server R Services 24
Installing and configuring  24
Server  25
Client 28
Making your solution operational  36
Using SQL Server R Services as a
compute context 36
Using stored procedures with R Code  40

Chapter 3: An end-to-end data science
process example 43
The data science process: an overview  44
The data science process in SQL Server R
Services: a walk-through for R and SQL
developers 47
Data and the modeling task 48
Preparing the infrastructure,
environment, and tools 51
Input data and SQLServerData object  65
Exploratory analysis 68
Data summarization 68
Data visualization 70
Creating a new feature (feature engineering)76
Using R functions 77
Using a SQL function  80
Creating and saving models  83
Using an R environment  84
Using T-SQL  86
Model consumption: scoring data with a
saved model 89
Evaluating model accuracy 95
Summary 97

Chapter 4: Building a customer churn
solution 98
Overview 99
Understanding the data  101
Building the customer churn model 105
Step-by-step 108
Summary 118


Chapter 5: Predictive maintenance and
the Internet of Things 120
What is the Internet of Things?  122
Predictive maintenance in the era of
the IoT  124
Example predictive maintenance use
cases 127
Before beginning a predictive
maintenance project 129
The data science process using SQL Server
R Services  132
Define objective 136
Identify data sources. 137
Explore data 140
Create analytics dataset 142
Create machine learning model  155
Evaluate, tune the model 157
Deploy the model  161
Summary 165

Chapter 6: Forecasting  167
Introduction to forecasting 169
Financial forecasting 169
Demand forecasting 170
Supply forecasting 171
Forecasting accuracy  171
Forecasting tools 173
Statistical models for forecasting 174
Time–series analysis 174
Time–series forecasting 179
Forecasting by using SQL Server R
Services 183
Upload data to SQL Server 183
Splitting data into training and testing 185
Training and scoring time–series
forecasting models 186
Generate accuracy metrics


Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud


This book does not have chapters 1 and 5, so don't think that you are going crazy


Chapter 2 Better security 1
Always Encrypted 1
Getting started with Always Encrypted 1
Creating a table with encrypted values 7
CREATE TABLE statement for encrypted columns 7
Migrating existing tables to Always Encrypted 9
Row-Level Security 11
Creating inline table functions 11
Creating security policies 14
Using block predicates 15
Dynamic data masking 15
Dynamic data masking of a new table 16
Dynamic data masking of an existing table 16
Understanding dynamic data masking and permissions 17
Masking encrypted values 18
Using dynamic data masking in SQL Database 18


Chapter 3 Higher availability 20
AlwaysOn Availability Groups 20
Supporting disaster recovery with basic availability groups 21
Using group Managed Service Accounts 23
Triggering failover at the database level 23
Supporting distributed transactions 24
Scaling out read workloads 25
Defining automatic failover targets 26
Reviewing the improved log transport performance 27
Windows Server 2016 Technical Preview high-availability enhancements 28
Creating workgroup clusters 28
Configuring a cloud witness 29
Using Storage Spaces Direct 32
Introducing site-aware failover clusters 32
Windows Server Failover Cluster logging 33
Performing rolling cluster operating system upgrades 33

Chapter 4 Improved database engine 35
TempDB enhancements   35
Configuring data files for TempDB 36
Eliminating specific trace flags 37
Query Store 38
Enabling Query Store 38
Understanding Query Store components 39
Reviewing information in the query store 40
Using Force Plan 42
Managing the query store 43
Tuning with the query store 44
Stretch Database 44
Understanding Stretch Database architecture 45
Security and Stretch Database 45
Identifying tables for Stretch Database 46
Configuring Stretch Database 47
Monitoring Stretch Database 48
Backup and recovery with Stretch Database 49

Chapter 6 More analytics 50
Tabular enhancements 50
Accessing more data sources with DirectQuery 51
Modeling with a DirectQuery source 51
Working with calculated tables 54
Bidirectional cross-filtering 56
Writing formulas 60
Introducing new DAX functions 60
Using variables in DAX 63
R integration 64
Installing and configuring R Services 64
Getting started with R Services 65
Using an R Model in SQL Server 74

Chapter 7 Better reporting 77
Report content types  77
Paginated report development enhancements 77
Introducing changes to paginated report authoring tools 78
Exploring new data visualizations 79
Managing parameter layout in paginated reports 84
Mobile report development  85
KPI development 85
Report access enhancements 86
Accessing reports with modern browsers 86
Viewing reports on mobile

You can get these books here: https://mva.microsoft.com/ebooks/

No, you are not getting the dark theme for SSMS anytime soon

At the SQL Server chalk talk session the question was asked when SQL Server Management Studio would get a dark theme. This is not an unreasonable question, after all, SQL Server Management Studio is built on top of the Visual Studio shell.

Here is what a light theme looks like in Visual Studio

And here is the same when I apply the dark theme

For me the light theme looks better...but what do I know
So why won't we get the dark theme for SQL Server Management Studio anytime soon?

The person answering the questions said that hears this asked all the time, he sees it on twitter, he sees it on facebook. He actually loves the dark theme in Visual Studio.

So the problem is that there are hundreds and hundreds of dialog boxes, wizards and views in SQL Server Management Studio that are not theme aware, those were written somewhere over the last 10 to 13 years.

They just finished the high DPI fix, that took hundreds if not thousand of code changes in files. So this is just basically a huge funding cost, priority wise it is really tough to justify doing this work, compared to feature requests and bug fixes.

So there you have it.... NO DARK THEME FOR YOU!!!!

Living in the NYC tri-state area? Attend the SQL 2016 Launch Event in NYC on June 2nd

Got this info from my co-worker Daryl earlier today.

On June 2nd Microsoft will hold a SQL 2016 Launch Event in New York City. The Microsoft Offices at 11 Times Square (at 8th and 42nd) in New York is where this event will be held.


WHEN
Thursday, June 2, 2016 from 8:00 AM to 2:00 PM (EDT)

WHERE
Microsoft Offices - 11 Times Square, New York, NY 10036

I might be there but I don't want to promise anything. You can sign up here for this event: https://www.eventbrite.com/e/sql-2016-launch-event-nyc-ny-tickets-25477835893

SQL Server 2016 will be generally available on June 1, 2016

Mark your calendars, SQL Server will be generally available less than a month from now, June 1st 2016.

You don't have a reason not to play around with SQL Server 2016, in March it was announced that the SQL Server 2016 Developer edition will also be available as a free download.

Also today, Lenovo published a new #1 TPC-H 30 TB world record using SQL Server 2016 and Windows Server 2016 on Lenovo System x2950 X6

To find out about all the difference between the various editions, download this pdf: SQL Server 2016 editions –what’s new 

For the official announcement, see here: Get ready, SQL Server 2016 coming on June 1st

How to get SQL Server on Linux?

In order to get SQL Server on Linux you need to go to the following page: https://www.microsoft.com/en-us/server-cloud/sql-server-on-linux.aspx. Click on the part where is says Sign up to stay informed and to apply for the preview. It will bring you to a signup page that looks like this

After you filled out all the information required you will have to wait for a reply. Be aware that currently the SQL Server on Linux preview is limited to Ubuntu or as a Docker image

SQL Server 2016 Release Candidate 0 now available for download

The SQL Server engineering team just announced the availability of SQL Server 2016 Release Candidate 0.


To learn more about the release, visit the SQL Server 2016 preview page. To experience the new, exciting features in SQL Server 2016 and the new rapid release model, download the preview and start evaluating the impact these new innovations can have for your business.


For more information on this release, see SQL Server 2016 Release Notes and What’s New in SQL Server 2016.

Some of the more interesting things added is that instead of a trace flag, you can now execute a 

ALTER DATABASE SCOPED CONFIGURATION command.

It looks like this

ALTER DATABASE SCOPED CONFIGURATION

{

[FOR SECONDARY]

{ SET  |  CLEAR PROCEDURE_CACHE }

}[;]

< set_options > ::=

{ MAXDOP = { | PRIMARY}

| LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}

| PARAMETER_SNIFFING = { ON | OFF | PRIMARY}

| QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}

| ONLINE_OPERATION_DEFAULT = {ON | OFF} }

There is also finally a built in split string function.

It looks like this

SELECT * FROM STRING_SPLIT('Hi how are you doing?',' ')

The result is:

value

—–

Hi

how

are

you

doing?

Finally, there will be an event on March 10, 2016: SQL Server 2016 — Take the lead, watch the live stream

Get the RC 0  here: SQL Server 2016 Release Candidate 0 

Gotcha with the LEAD analytical function and date datatype

The other day some code that used the LEAD analytical function stopped working suddenly. It started to throw an error.

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

The code wasn't touched in a long time... so what could be the issue. Let's reproduce it here

First create the following table

CREATE TABLE testRunningTotal(Id tinyint,SomeDate datetime, Charge decimal(20,2))

INSERT testRunningTotal
SELECT 1,'20120101',1000
UNION ALL
SELECT 1,'20120401',200
UNION ALL
SELECT 1,'20120501',300
UNION ALL
SELECT 1,'20120601',600
UNION ALL
SELECT 2,'20120101',100
UNION ALL
SELECT 2,'20130101',500
UNION ALL
SELECT 2,'20140101',-800
UNION ALL
SELECT 3,'20120101',100

Running this query to see what is in this table

SELECT * FROM testRunningTotal

Id SomeDate Charge

1 2012-01-01 00:00:00.000 1000.00

1 2012-04-01 00:00:00.000 200.00

1 2012-05-01 00:00:00.000 300.00

1 2012-06-01 00:00:00.000 600.00

2 2012-01-01 00:00:00.000 100.00

2 2013-01-01 00:00:00.000 500.00

2 2014-01-01 00:00:00.000 -800.00

3 2012-01-01 00:00:00.000 100.00

Now run our fancy query...

SELECT id, someDate as StartDate,
LEAD(SomeDate -1,1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

Here are our results

id StartDate Enddate Charge RunningTotal

1 2012-01-01 00:00:00.000 2012-03-31 00:00:00.000 1000.00 1000.00

1 2012-04-01 00:00:00.000 2012-04-30 00:00:00.000 200.00 1200.00

1 2012-05-01 00:00:00.000 2012-05-31 00:00:00.000 300.00 1500.00

1 2012-06-01 00:00:00.000 9999-12-31 00:00:00.000 600.00 2100.00

2 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 100.00 100.00

2 2013-01-01 00:00:00.000 2013-12-31 00:00:00.000 500.00 600.00

2 2014-01-01 00:00:00.000 9999-12-31 00:00:00.000 -800.00 -200.00

3 2012-01-01 00:00:00.000 9999-12-31 00:00:00.000 100.00 100.00

To see what the query actually does, visit this post Easy running totals with windowing functions

Let's take a closer look at the LEAD analytical function, this is what Books On Line has on its page

  LEAD ( scalar_expression [ ,offset ] , [ default ] ) 

    OVER ( [ partition_by_clause ] order_by_clause )

offset

The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

Looks all cool to me..nothing that stands out that could cause a problem...

What will happen if someone changes the column from a datetime to a date? Maybe they looked at the data and saw that all the values had only dates and no time components... why use 8 bytes of storage when you can use 3 instead?

To find out exactly what will happen, run the following DDL statement....

ALTER TABLE testRunningTotal ALTER COLUMN SomeDate date

Now go run your query again

SELECT id, someDate as StartDate,
LEAD(SomeDate -1,1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

Here is the error you get

  Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

The error is the same as if you would run the following

SELECT CONVERT(DATE,GETDATE() )-1

Instead what you have to do is use the DATEADD function to do arithmetic with dates

SELECT DATEADD(dd,-1,CONVERT(DATE,GETDATE()))

So to fix our query from before, we want to change the -1 to be DATEADD(dd,-1 instead, here is what our query looks like

 SELECT id, someDate as StartDate,
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

And here are our results, it's all good now

id StartDate Enddate Charge RunningTotal

1 2012-01-01 2012-03-31 1000.00 1000.00

1 2012-04-01 2012-04-30 200.00 1200.00

1 2012-05-01 2012-05-31 300.00 1500.00

1 2012-06-01 9999-12-31 600.00 2100.00

2 2012-01-01 2012-12-31 100.00 100.00

2 2013-01-01 2013-12-31 500.00 600.00

2 2014-01-01 9999-12-31 -800.00 -200.00

3 2012-01-01 9999-12-31 100.00 100.00

I still think that maybe the LEAD function should do this conversion internally since all we are doing is specifying the OFFSET...what is your opinion?

Easy running totals with windowing functions

Back in the pre SQL Server 2012 days, if you wanted to do a running count, you either had to run a subquery or you could use a variable. This was slow because for each row the query that did the sum would be executed. With the additions to the windowing functions in SQL Server 2012, this is now running much faster. 

Let's take a look, first create the following table

CREATE TABLE #test(Id tinyint,SomeDate date, Charge decimal(20,10))

insert #test
SELECT 1,'20120101',1000
UNION ALL
SELECT 1,'20120401',200
UNION ALL
SELECT 1,'20120501',300
UNION ALL
SELECT 1,'20120601',600
UNION ALL
SELECT 2,'20120101',100
UNION ALL
SELECT 2,'20130101',500
UNION ALL
SELECT 2,'20140101',-800
UNION ALL
SELECT 3,'20120101',100

let's check that data we just inserted into the temporary table

SELECT * FROM #test

The output looks like this

Id SomeDate Charge
1 2012-01-01 1000.0000000000
1 2012-04-01 200.0000000000
1 2012-05-01 300.0000000000
1 2012-06-01 600.0000000000
2 2012-01-01 100.0000000000
2 2013-01-01 500.0000000000
2 2014-01-01 -800.0000000000
3 2012-01-01 100.0000000000

What we want is the following

id StartDate Enddate         Charge         RunningTotal
1 2012-01-01 2012-03-31 1000.0000000000 1000.0000000000
1 2012-04-01 2012-04-30 200.0000000000 1200.0000000000
1 2012-05-01 2012-05-31 300.0000000000 1500.0000000000
1 2012-06-01 9999-12-31 600.0000000000 2100.0000000000
2 2012-01-01 2012-12-31 100.0000000000 100.0000000000
2 2013-01-01 2013-12-31 500.0000000000 600.0000000000
2 2014-01-01 9999-12-31 -800.0000000000 -200.0000000000
3 2012-01-01 9999-12-31 100.0000000000 100.0000000000

For each row, we want to have the date that the row starts on and also the date when it end, we also want a running total as well. If there is no row after the current row for that id, we want the end date to be 9999-12-31.

So we will use a couple of functions. The first one is LEAD, LEAD accesses data from a subsequent row in the same result set without the use of a self-join. So the LEAD part looks like this

LEAD(dateadd(dd,-1,SomeDate),1,'99991231')  OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,

What we are doing is subtracting 1 from the date in the subsequent row (ateadd(dd,-1,SomeDate))
We are using 1 as the offset since we want to apply this to the next row. Finally if there is no subsequent row, we want to use the date 9999-12-31 instead of NULL

To do the running count, we will do the following

SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          AS RunningTotal

What this means in English is for each id ordered by date, sum up the charge values for the rows between the preceding rows and the current row. Here is what all that stuff means.

ROWS BETWEEN
Specifies the rows that make up the range to use as implied by

UNBOUNDED PRECEDING
Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as window starting point.

CURRENT ROW
Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE.
CURRENT ROW can be specified as both a starting and ending point.

And here is the query

SELECT id, someDate as StartDate,
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM #test
  ORDER BY id, SomeDate

And running that query, gives us the running count as well as the dates

id StartDate Enddate         Charge         RunningTotal
1 2012-01-01 2012-03-31 1000.0000000000 1000.0000000000
1 2012-04-01 2012-04-30 200.0000000000 1200.0000000000
1 2012-05-01 2012-05-31 300.0000000000 1500.0000000000
1 2012-06-01 9999-12-31 600.0000000000 2100.0000000000
2 2012-01-01 2011-12-31 100.0000000000 100.0000000000
2 2012-01-01 2011-13-31 500.0000000000 600.0000000000
2 2012-01-01 9999-12-31 -800.0000000000 -200.0000000000
3 2012-01-01 9999-12-31 100.0000000000 100.0000000000

That's all for this post...

TempDB Database changes in SQL Server 2016

According to Books On Line, there are several changes made in SQL Server 2016 in regards to TempDB. Here is what Books On Line has on the topic

There are several enhancements to TempDB:

• Trace Flags 1117 and 1118 are not required for tempdb anymore. If there are multiple tempdb database files all files will grow at the same time depending on growth settings. In addition, all allocations in tempdb will use uniform extents.
• By default, setup adds as many tempdb files as the CPU count or 8, whichever is lower.
• During setup, you can configure the number of tempdb database files, initial size, autogrowth and directory placement using the new UI input control on the Database Engine Configuration - TempDB section of SQL Server Installation Wizard.
• The default initial size is 8MB and the default autogrowth is 64MB.
• You can specify multiple volumes for tempdb database files. If multiple directories are specified tempdb data files will be spread across the directories in a round-robin fashion.

I decided to check this out, I downloaded and installed SQL Server 2016 Community Technology Preview 3.0 During the install I did not make any changes to TempDB

Once the install was done, I executed the following to see how many files TempDB had on my laptop

exec sp_helpdb 'tempdb'

Here is what I saw in the output

name fileid filename filegroup size maxsize growth usage
tempdev 1 C:\DATA\tempdb.mdf PRIMARY 8192 KB Unlimited 65536 KB data only
templog 2 C:\DATA\templog.ldf NULL 8192 KB Unlimited 65536 KB log only
temp2 3 C:\DATA\tempdb_mssql_2.ndf PRIMARY 8192 KB Unlimited 65536 KB data only
temp3 4 C:\DATA\tempdb_mssql_3.ndf PRIMARY 8192 KB Unlimited 65536 KB data only
temp4 5 C:\DATA\tempdb_mssql_4.ndf PRIMARY 8192 KB Unlimited 65536 KB data only

As you can see, there are 4 data files, when I looked at my SQL Server 2014 instance which was installed with defaults, there is only 1 data file. Another difference is that SQL Server 2014 uses 10% growth as default while SQL Server 2016 uses 65536 KB

This is a good change for all the casual installs by people who just want to experiment and learn, however most shops I would think already would have changed TempDB to have more than 1 file. But then again maybe Microsoft noticed that a lot of support calls that they are getting have only 1 data file for TempDB and thus they decided to implement this change.

SQL Server 2016 Community Technology Preview 3.0 is now available for download

Public service announcement!!!!!

SQL Server 2016 Community Technology Preview 3.0 is now available for download. The download is 2.6 GB, you can get it here SQL Server 2016 Community Technology Preview 3.0


I am already downloading mine.... what are you waiting for?

The stuff I am most excited about in this CTP is the SQL Server R Services in SQL Server 2016....


To accompany CTP3, the SQL Server team also created an updated version of AdventureWorks, called AdventureWorks2016CTP3, and they have created a number of samples to showcase some of the new functionality in SQL2016. They recommend you download and install the sample databases and play with the sample scripts. Download link: AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3

SQL Server 2016 Community Technology Preview 3.0 will be available this week

SQL Server 2016 Community Technology Preview 3.0 will be available this week.

Some of the new and exciting stuff......


Advanced Analytics (RRE integration)

With this release, we are very excited to announce the public availability SQL Server R Services in SQL Server 2016, an Advanced Analytics capability which supports enterprise-scale data science, significantly reducing the friction for adopting machine learning in your business. SQL Server R Services is all about helping customers embrace the highly popular open source R language in their business. R is the most popular programming language for Advanced Analytics. You can use it to analyze data, uncover patterns and trends and build predictive models. It offers an incredibly rich set of packages and a vibrant and fast-growing developer community. At the same time, embracing R in an enterprise setting presents certain challenges, especially as the volume of data rises and with the switch from modeling to production environments. Microsoft SQL Server R Services with in-database analytics helps customers embrace this technology by supporting several scenarios. 

Transactional replicate from SQL Server to Azure SQL DB 

This is new in CTP3. Now you can setup Azure SQL DB as a subscriber of transaction replication, allowing you to migrate data from SQL Server instance on-premises or in IaaS to Azure SQL database without downtime. The replication is one way in this release, and works with SQL Server 2016, SQL Server 2014 and SQL Server 2012. This is the same Transactional Replication technology you have been using for many years on premise. As you configure a subscriber (from SSMS or by script), instead of entering an instance name, you enter the name of your Azure SQL DB subscription along with the associated login and password. A snapshot (as in a Replication Snapshot) will used to initialize the subscription and subsequent data changes will be replicated to you Azure SQL DB in the same transactional consistent way you are used to. A transactional publication can deliver changes to subscribers both in Azure SQL DB and/or on premise/Azure VM. There is no Replication service hosted in Azure for this. Everything is driven from on-premise distribution agents. To use this feature, you just need to set it up the way you do to replicate on-premises: Install the Replication components, configure the Distributor, the Publisher and create the Publication, the Articles and you the Subscriptions. In this case, one of the subscriptions will be your Azure SQL DB.

In-Memory improvements in this release:

• In-Memory OLTP
  
  • FOREIGN KEY constraints
  • CHECK constraints
  • UNIQUE constraints
  • DML triggers (AFTER only)
  • EXECUTE AS CALLER
  • Inline table-values functions
  • Security built-ins and increased math function support
• Real-time Operational Analytics
  
  • Support for in-memory tables
  • Existing nonclustered columnstore index (NCCI) are updateable without requiring index rebuild
  • Parallel Index build of nonclustered columnstore index (NCCI)
  • Performance improvements (INSERT, String pushdown, bypassing delete buffer when processing deleted rows)
• In-Memory Analytics
  • You can upgrade databases with nonclustered columnstore index and have it updateable without requiring rebuild of the index
  • General performance improvements for analytics queries with columnstore index especially involving aggregates and string predicates
  • Improved supportability with DMVs and XEvents

Stretch Database updates in this release:

• Engine Update
  • Create/Drop index support
  • AlwaysEncrypted support
  • Improved credential management for remote Stretch database stores
  • Improved performance for joins between stretched tables
  • New external data source integration
• SSMS Wizard updates
  
  • Database and Table level fly out menu options were updated to reflect new Stretch functionality
  • Stretch monitor functionality added to allow users to monitor current migration status, including the ability to pause the migration at the table level
  • XEVENT support for diagnostics session support in monitor
  • Updated and simplified stretch wizard flow to reduce the amount of steps required to enable or reconfigure Stretch
  • Help icon links Updated to point to new MSDN content focusing specifically on wizard topic
  • Added functionality that allows users to pause or disable migration at the table level
  • Added ability to Stretch individual tables
  • Added database scoped credential support - for AlwaysOn
  • Ability to enabling stretch on the server using the wizard
  • Updated table level validation error/warning messaging
  • The ability to Stretch to new SQL Azure or existing SQL Azure server
  • Updated SSMS Object Explorer Stretch Databases icons
  • SMO model for Stretch status query and updates

Polybase in CTP3 includes the following new capabilities:

• Improved PolyBase query performance with scale-out computation on external data (PolyBase scale-out groups)
• Improved PolyBase query performance with faster data movement from HDFS to SQL Server and between PolyBase Engine and SQL Server
• Support for exporting data to external data source via INSERT INTO EXTERNAL TABLE SELECT FROM TABLE
• Support for push-down computation to Hadoop for string operations (compare, LIKE)
• Support for ALTER EXTERNAL DATA SOURCE statement

Built-in JSON support improvements in this release include:

• OPENJSON - Table value function that parses JSON text and returns rowset view of JSON. By default, OPENJSON returns properties of object or elements of array that is parsed. Advanced version of OPENJSON function with defined schema allows user to define schema of resulting rowset, and mapping rules that define where can be found values in the parsed JSON text that will be returned in the resulting rowset. It enables developers to easily parse JSON text and import it into relational tables.
• JSON_VALUE - Scalar function that returns a value from JSON on the specified path. It can be used in any query, view, computed column. It can be also used to define indexes on properties of JSON text stored in table columns.
• ISJSON - function that validates that JSON is properly formatted. It can be used to define check constraints on the columns that contain JSON text. It is not supported in check constraints defined on in-memory tables.
• JSON_QUERY - Scalar function that returns a fragment from the JSON text. Unlike JSON_VALUE that returns scalar values, JSON_QUERY returns complex object (i.e. JSON arrays and objects).

Temporal support improvements in this release include:

• Support for using temporal system-versioning with In-Memory OLTP
  • Combining disk-based table for cost-effective storing of history data with memory-optimized tables for storing latest (actual) data
  • Super-fast DML and current data querying supported from natively compiled code
  • Temporal querying supported from interop mode
  • Internal in-memory table created to minimally impact performance of DML operations
  • Background process that flushes the data from internal in-memory to permanent disk-based history table
• Direct ALTER for system-versioned temporal tables enables modifying table schema without introducing maintenance window
  • Support for adding/altering/dropping columns while SYSTEM_VERSIONING is ON
  • Support for ADD/DROP HIDDEN for period columns while SYSTEM_VERSIONING is ON
• Support for temporal querying clause FOR SYSTEM_TIME ALL that enables users to query entire data history easily without specifying period boundaries
• Optimized CONTAINED IN implementation with minimized locking on current table. If your main case is analysis on historical data only, use CONTAINED IN.

Query Store improvements in this release include:

• Performance monitoring supported for natively compiled code from In-Memory OLTP workloads:
  • Collecting queries, plans and compile time statistics enabled for natively compiled queries when Query Store is ON
  • Stored plan is semantically equivalent to one that is produced when SET SHOWPLAN_XML is set to ON with one difference: plans in Query Store are always split and stored per individual statement
  • Runtime statistics collection is controlled with sys.sp_xtp_control_query_exec_stats (does not enabled by default)
  • is_natively_compiled field added to sys.query_store_plan to help finding queries  generated by the native code compilation
  • Plan forcing for queries from natively compiled modules is available and forced plans are honored during module recompilation. As for disk-based workloads, Query Store does not guarantee success of plan forcing operation as some plan shapes cannot be forced
  • Memory grants metrics within sys.query_store_runtime_stats are not populated for natively compiled queries – their values are always 0
• Improving implementation of time-based cleanup (configured with STALE_QUERY_THRESHOLD_DAYS) to run in multiple transactions, holding database lock for a shorter period of time and thus minimize impact on customer workload
• Hadoop Connector: SSIS Hadoop connector allows customer to copy data to/from HDFS and trigger Hive/Pig job on Hadoop cluster. This brings in following components: Hadoop Connection Manager, H

SQL Server Integration Services (SSIS) improvements in this release include:

• SSIS control flow template enables customers to save a commonly used control flow task or container to a standalone template file and reuse it multiple times in a package or multiple packages in a project. This reusability introduced by template makes SSIS packages easier to design and maintain.
• Added Azure blob source support for the Import/Export wizard; user can use Azure blob source as source or destination during the transformation.
• Relaxed Max Buffer Size of Data Flow Task. The max Default Buffer Size of Data Flow Task is relaxed to 2G-1 from 100M. A new attribute 'AutoAdjustBufferSize' is added to Data Flow Task, which can be set in SSDT. If it is set to true, the Default Buffer Size will be set automatically in runtime according to Default Buffer Max Rows.

SQL Server Analysis Services (SSAS) improvements in this release include the following; please visit the SSAS team blog to learn more.

• DBCC support
• The Microsoft.AnalysisServices library has been re-factored to include a second namespace, Microsoft.AnalysisServices.Core. The new namespace separates out common classes like Server, Database, and Role that have broad application in Analysis Services, irrespective of server mode.
• SSMS and SSDT updates for Tabular

SQL Server Reporting Services (SSRS) improvements in this release include the following; please visit the SSRS team blog to learn more.

• Pin Reporting Services report items - Including charts, gauge panels, maps, and images - to Power BI dashboards. Dashboard tiles always show up-to-date data thanks to scheduled refresh. Click a dashboard tile to drill through to the complete Reporting Services report.
• Design reports using Visual Studio 2015 with an updated version of SQL Server Data Tools.
• Uses .NET Framework 4.x code in report expressions, report code, referenced assemblies, and extensions for report security, data processing, rendering, or delivery.

Master Data Services (MDS) improvements in this release include:

• Entity Change Approve Flow. Admin can mark an entity requiring approvals for changes. Examples include:
  • Admin marks an entity requiring approval for changes in the entity administrator page
  • User needs to save pending change to change set and submit to admin for approval
  • Admin approves or rejects the pending changes
  • The approved pending changes will be committed to the master data services
• Domain Based Attribute Constrained List. For domain-based attributes, optionally, user can select a parent attribute whose value will constrain the allowed values for this attribute. Examples include:
  
  • Model has State, City, Account entity
  • Account has a City DBA to City entity and State DBA to State entity
  • City has a State DBA to State entity and a derived hierarchy from State to City
  • A constrain can be added on Account; City attribute which parent is Account.State. so the City attribute dropdown list is constrained by State value

Query memory grant enhancement improvement in CTP3:

• Updated sort_warning and hash_warning XEvent to include spill IO stats
• New hash_spill_details XEvent for detailed spill information
• Statistics XML and SSMS updated to include spill warning
• sys.dm_exec_query_stats DMV: updated with new memory grant and parallelism info
• sys.dm_exec_requests DMV: updated with new memory grant and parallelism info
• sys.dm_exec_query_parallel_workers: new DMV to show worker threads status for parallel queries
• sys.dm_exec_query_optimizer_memory_gateways: new DMV to show query optimizer compile gates

To see when SQL Server 2016 Community Technology Preview 3.0 will be available visit this page  http://www.microsoft.com/en-us/server-cloud/products/sql-server-2016/default.aspx