Transcript Standard/Express edition

Standard/Express edition
• Faster transaction performance from In-memory OLTP, faster query
performance from In-memory ColumnStore, and the ability to combine the
two for real-time Hybrid Transactional and Analytical Processing, also
known as Operational Analytics
• Data warehousing or data mart performance features such as partitioning,
compression, change data capture, database snapshot, and the ability to
query across structured and unstructured data with a single node of
PolyBase
• The innovative security feature Always Encrypted for encryption at rest and
in motion, as well as fine-grained auditing which captures more detailed
audit information for your compliance reporting needs. In addition, rowlevel security and dynamic data masking are being made available to
Express edition for the first time
Create or alter
• STORED PROCEDURES
• FUNCTIONS
• TRIGGERS
• VIEWS
• just like "drop if exists“ for tables
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/11/17/create-or-alteranother-great-language-enhancement-in-sql-server-2016-sp1/
DBCC CLONEDATABASE
Creates a new database
• schema of all the objects, and
• statistics
from the specified source database
• SQL Server 2014 SP1
• SQL Server 2016 SP1
http://www.pontop.dk/single-post/2016/07/12/TSQL-Tuesday-80-I-want-DBCCCLONEDATABASE-available-on-all-supported-versions-of-SQL-Server
Lock Pages in Memory
DMV columns
SELECT sql_memory_model, sql_memory_model_desc
FROM sys.dm_os_sys_info;
Instant File Initialization
DMV columns
SELECT instant_file_initialization_enabled
FROM sys.dm_server_services;
SQL Server 2016SP1 on Windows Server 2016
SQL Server Tail Of Log Caching
on NVDIMM (Non-Volatile Dual Inline Memory Module)
https://blogs.msdn.microsoft.com/bobsql/2016/11/08/how-it-works-it-just-runs-faster-nonvolatile-memory-sql-server-tail-of-log-caching-on-nvdimm/
References
• https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/1
6/sql-server-2016-service-pack-1-generally-available/
• https://msdn.microsoft.com/en-us/library/cc645993.aspx
• https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1
• https://www.mssqltips.com/sqlservertip/4574/new-features-in-sqlserver-2016-service-pack-1/