Skip to content

SQLServerF1

SQLServerF1 – In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

  • Home
  • SQL Server FAQ
  • SQL Server Errors
  • SQL Server Events
  • SQL Server Trainings
  • Disclaimer

Category: ColumnStore Index

Introduction to New SQL Server 2016 Features

May 28, 2015 SQLServerF1 Leave a comment
SQLServerF1

Recently Microsoft has made an announcement about the upcoming release of SQL Server 2016. There has been great enthusiasm about features that will be released with the new SQL Server 2016 and the enhancements that are made to the new SQL Server 2016. Below are list of some of the important and useful features introduced with SQL Server 2016 or enhancements made to existing features in SQL Server 2016.
ColumnStore Index – Below are the new enhancements made to the ColumnStore Index in SQL Server 2016. This feature was first introduced in SQL Server 2012, and few enhancements were made in SQL Server 2014 and some great new enhancements are made in SQL Server 2016 as mentioned below.
– Updatable NonClustered ColumnStore Index
– ColumnStore Index on In-Memory Tables
– Single-Thread Vs. Multi-Thread Batch Execution
– Snapshot and Read-Commited Snapshot Isolation
Specify Column in Table Creation

· AlwaysOn Availability Groups – There have been some very important and useful changes to the AlwaysON feature. This feature was introduced in SQL Server 2012 and some enhancements were made in SQL Server 2014 and now in SQL Server 2016 there have been further enhancements as mentioned below.
o DTC – beginning for SQL Server 2016 Community Technology Preview 2 (CTP2), cross-database transactions are supported for AlwaysOn Availability Groups running on Windows Server 2016 Technical Preview 2.
o Automatic Failover replicas have been increased from 2 replicas to 3 replicas.
o Group Managed Service Account –
o Load Balance of Read-Intent – Beginning with SQL Server 2016 Community Technology Preview 2 (CTP2), you can configure load-balancing across a set of read-only replicas.

References – https://msdn.microsoft.com/en-s/library/hh710054%28v=sql.130%29.aspx#loadbalancing,
https://technet.microsoft.com/library/hh831782.aspxHope this was helpful.

· In-Memory OLTP – This feature was first introduced with SQL Server 2014 and there are many new enhancements introduced with SQL Server 2016 as mentioned below.
– Alter Operation – Alter Table and Alter Procedure
– User-Defined Function
– All Collation and Code Pages
– Throughput Increase
– TDE on In-Memory Database
– Multiple threads to persist memory-optimized tables
– Multi-threaded recovery
– Multi-threaded MERGE Operation
– Stored Procedure Check Tables to go to In-Memory
– More Surface Area on Natively Stored Procedures
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx

· Live Query Statistics – Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another.
Reference – https://msdn.microsoft.com/en-us/library/dn831878%28v=sql.130%29.aspx

· Query Store – Query store is a new feature in that provides DBAs with insight on query plan choice and performance. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review.
Reference – https://msdn.microsoft.com/en-us/library/dn817826%28v=sql.130%29.aspx
· Temporal Tables – https://msdn.microsoft.com/en-us/library/dn935015%28v=sql.130%29.aspx

· Backup to Microsoft Azure – SQL Server backup to URL using Microsoft Azure Blob storage services now supports using block blobs instead of page blobs.
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Managed Backup – In SQL Server 2016 Community Technology Preview 2 (CTP2) SQL Server Managed Backup to Microsoft Azure uses the new block blob storage for backup files. There are also several changes and enhancements to Managed Backup.
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Multiple TempDB Files – Setup adds multiple tempdb data files during the installation of a new instance
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Transparent Data Encryption Enhancements – TDE has been enhanced with support for Intel AES-NI hardware acceleration of encryption. This will reduce the CPU overhead of turning on Transparent Data Encryption.
Reference – https://msdn.microsoft.com/en-us/library/bb510411%28v=sql.130%29.aspx
· Truncate Table with Partitions – The TRUNCATE TABLE statement now permits the truncation of specified partitions.
Reference – https://msdn.microsoft.com/en-us/library/ms177570%28v=sql.130%29.aspx
· Always Encrypted – With Always Encrypted, SQL Server can perform operations on encrypted data, and best of all the encryption key resides with the application inside the customer’s trusted environment and not on the server.
Reference – https://msdn.microsoft.com/en-us/library/mt163865%28v=sql.130%29.aspx
· Row-Level Security – Row level security introduces predicate based access control. It features a flexible, centralized, predicate-based evaluation that can take into consideration metadata or any other criteria the administrator determines as appropriate.
Reference – https://msdn.microsoft.com/en-us/library/dn765131%28v=sql.130%29.aspx
· Dynamic Data Masking – Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer.
Reference – https://msdn.microsoft.com/en-us/library/mt130841%28v=sql.130%29.aspx
· Stretch Database – Stretch Database is a new feature in SQL Server 2016 Community Technology Preview 2 (CTP2) that leverage’s resources in Windows Azure to store and query archival data.
Reference – https://msdn.microsoft.com/en-us/library/dn935011%28v=sql.130%29.aspx

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
  • AlwaysON
  • ColumnStore Index
  • In-Memory OLTP
  • SQL Server
  • SQL Server 2016
  • SQL Server Performance

Polls

Which Relational Database Management System Do you Like?

View Results

Loading ... Loading ...

Categories

  • AlwaysON (13)
  • Backup/Restore (21)
  • Blocking (2)
  • Cloud (19)
  • Cluster Shared Volumes (3)
  • ColumnStore Index (1)
  • Connectivity (13)
  • Database Engine (86)
  • Database File Gorw/Shrink (4)
  • Database Mail (1)
  • Database Mirroring (2)
  • Database Snapshot (1)
  • DBCC Commands (6)
  • Encryption (4)
  • Frequently Asked Question and Answers (9)
  • General (18)
  • Import/Export (2)
  • In-Memory OLTP (3)
  • Informatica Errors (459)
  • LogShipping (5)
  • Maintenance Plans (4)
  • MYSQL Errors (168)
  • Operating System (40)
  • Other Products Errors (2,381)
  • Replication (3)
  • SAP Errors (1,662)
  • Security (15)
  • SQL Azure (14)
  • SQL Azure Errors (17)
  • SQL Server (346)
  • SQL Server 2008 R2 (15)
  • SQL Server 2012 (17)
  • SQL Server 2014 (38)
  • SQL Server 2016 (34)
  • SQL Server Analysis Services (2)
  • SQL Server Best Practices (9)
  • SQL Server Browser (2)
  • SQL Server Cluster (9)
  • SQL Server Errors (662)
  • SQL Server Indexes (1)
  • SQL Server Integration Services (1)
  • SQL Server Jobs (4)
  • SQL Server Known Issues (103)
  • SQL Server Management Studio (8)
  • SQL Server Memory (2)
  • SQL Server Monitoring (7)
  • SQL Server on Linux (2)
  • SQL Server Performance (28)
  • SQL Server Reporting Services (1)
  • SQL Server Startup (11)
  • SQL Server Waits (23)
  • SQL Server/Service Pack Installation (42)
  • Sybase Errors (106)
  • TeraData Errors (500)
  • Third-Party Tools for SQL Server (24)
  • TraceFlags (7)

Archives

  • November 2020
  • August 2017
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • December 2015
  • November 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • July 2014

Recents Posts

  • Scrambled Database refreshes in SQL ServeR
  • SQL Server Consolidation Frequently Asked Questions and Answers
  • The login is From an Untrusted Domain and Cannot be Used with Windows Authentication
  • Updates About SQL Server 2016 on Linux
  • Details about EXECSYNC Wait Type in SQL Server
January 2021
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031
« Nov    

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Recent Comments

  • Arun on SQL Server 2012 SP1 Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 537, State 3, Severity 16
  • Raja raman on SAP Error Messages and Error Codes from SAP Error 12-109 to 12-118
  • Almeida on SQL Server 2012 SP1 Upgrade Step ‘msdb110_upgrade.sql’ Encountered Error 537, State 3, Severity 16
  • Lydia on SQL Server Installation Wizard disappears After Setup Support Files
  • Tejas Shah on Oracle Database Errors or Warnings from Error ORA-02851 to ORA-02878

RSS Related News

  • Disclaimer
Powered by WordPress and Dynamic News.