SQL Server 2016 New Innovations

56 Pages·2015·3.04 MB·English

SQL Ser ver 2016
New innovations
César Mendes
[email protected]
Partner Technical Consultant Data Platform Airlift
21 de Outubro \\ Microsoft Lisbon Experience
Data differentiates today's leading companies
Speed
Source: IDC & Microsoft, April 2014
Microsoft platform leads the way on-premises and cloud
Leader in 2014 for Gartner Magic Quadrants
Operational Data Business x86 Server Cloud Enterprise Public Cloud
Database Warehouse Intelligence Virtualization Infrastructure Application Storage
Management Database and Analytics as a Service Platform as a
Systems Management Platforms Service
Systems
Do more. Achieve more.
Mission-critical performance
Performance Security Availability Scalability
Operational analytics Always encrypted Enhanced AlwaysOn Enhanced database
Insights on operational data; Sensitive data remains encrypted Three synchronous replicas for caching
Works with in-memory OLTP and at all times with ability to query auto failover across domains
Cache data with automatic,
disk-based OLTP
Round robin load balancing of multiple TempDB files per instance
Row-level security
In-memory OLTP replicas in multi-core environments
Apply fine-grained access control
enhancements Automatic failover based on
to table rows
database health
Greater T-SQL surface area,
Dynamic data masking DTC for transactional integrity
terabytes of memory supported,
across database instances with
Real-time obfuscation of data to
and greater number of parallel
AlwaysOn
prevent unauthorized access
CPUs
Support for SSIS with AlwaysOn
Other enhancements
Query data store
Audit success/failure of database
Monitor and optimize query plans
operations
TDE support for storage of in-
Native JSON
memory OLTP tables
Expanded support for JSON data
Enhanced auditing for OLTP with
Temporal database ability to track history of record
changes
support
Query data as points in time
In-memory OLTP
enhancements
ALTER support
ALTER TABLE Sales.SalesOrderDetail
ALTER INDEX PK_SalesOrderID Full schema change support: add/alter/drop
REBUILD column/constraint
WITH (BUCKET_COUNT=100000000)
Add/drop index supported
Surface area improvements
T-SQL surface area: New
Almost full T-SQL coverage including scaler user-defined
functions
{LEFT|RIGHT} OUTER JOIN
Disjunction (OR, NOT)
Improved scaling
UNION [ALL]
SELECT DISTINCT Increased size allowed for durable tables; more sockets
Subqueries (EXISTS, IN, scalar)
Other improvements
MARS support
Lightweight migration reports
Altering natively compiled stored procedures
CREATE PROCEDURE [dbo].[usp_1] You can now perform ALTER
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
operations on natively compiled
AS BEGIN ATOMIC WITH
(
stored procedures using the
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE =
N'us_english' ALTER PROCEDURE statement
)
SELECT c1, c2 from dbo.T1
END Use sp_recompile to
GO
recompile stored procedures on
ALTER PROCEDURE [dbo].[usp_1]
the next execution
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE =
N'us_english'
)
SELECT c1 from dbo.T1
END
GO
Using multiple active result sets (MARS)
Data Source=MSSQL; Initial Catalog=AdventureWorks; Setup MARS connection for
Integrated Security=SSPI;
memory optimized tables using
MultipleActiveResultSets=True
the
MultipleActiveResultsSets
=True in your connection string
Query Store
Your flight data recorder
for your database