New Features Added in SQL Server

This post helps you in quickly reviewing the “New Features Added in SQL Server” from 2008 to 2016. The most common interview question is “What are the new features added in SQL Server XXXX?” To simplify the answer we are just giving the single line abbreviations. While preparing for an interview just have a quick look and try to remember 4 to 5 features on current working and last released version. Let’s say you are currently working on 2012 then you need to look for 2012 and 2008 R2.   Also people would expect you to know few new features added in the latest version which is SQL Server 2016. Features are categorised for DBA, Developer and Security. That doesn’t mean that SQL Developer need not look into DBA section or vice versa. This is just to make it more readable, when a feature is more related to Developer we added in Developer section when a feature is more relevant administration part it will be in DBA section. Believe this will be helpful for a quick review for version wise feature support.

New Features Added in SQL Server 2008

SQL DBA:

Activity Monitor: Great tool to showcase resource utilization and performance using GUI.

Policy Based Management: The ability to manage standards on multiple servers

Enhanced Database Mirroring: Automatic data page repair and compressing outgoing log stream

Resource Governor: We can configure it to control SQL Server resource utilization and workload.

External Key Management: Provides a comprehensive solution for encryption and key management.

Hot Add CPU: Adding resources online without downtime.

PowerShell: SQL Server 2008 ships with PowerShell snap-in built on .Net framework 2.0.

Table Compression: Compress data and index pages to save memory and I/O.

Backup Compression: Native backup compression.

Performance data collection: Centralized data repository for storing and reporting performance data

Extended Events: Event collection is easier now compares to running a trace

SQL Developer:

File-stream Data: To store binary large objects out on the file system.

Multi-Server Queries: being able to submit a batch of queries against multiple servers simultaneously.

Object Explorer Details: 36 possible columns of information about a database

Object Search: Searches for all objects within the selected scope: server, database, tables

SSMS Debug: the ability to debug any Transact-SQL within SSMS using breakpoints etc.

Intellisense in SQL Server Management Studio: interactive help support similar to Visual Studio.

Plan Freezing: Now we can lock down query plans

Spatial Data Types: Geometry and Geography

DATE / TIME Data Types: DATE, TIME, DATETIMEOFFSET

CLR Enhancements: User-defined aggregates and User-defined types now enhanced up to 2GB.

Table-Valued Parameters: It allows stored procedures to accept and return lists of parameters.

MERGE command: For incremental load using INSERT, UPDATE and DELETE operations.

HierarchyID Datatype: Provides tree like functionality among the data elements in a table

Grouping Sets: An extension to the GROUP BY

Filtered Indexes and Statistics: We can create non clustered index on a subset of a table

Object Dependencies: New DMV’s provided for reliable information on depending objects

Sparse Columns: Columns that are optimized for the storage of NULL values.

Security:

TDE – Transparent Database Encryption: Encrypt database without code changes

Change Data Capture: Track data changes.

SQL Auditing: the ability to audit at the server, database and table levels.

New Features Added in SQL Server 2008 R2

SQL DBA:

SQL Server 2008 R2 Datacenter: Supports 256 logical processors

SQL Server Utility: Central repository control for multiple SQL Servers

Multi Server Dashboards: Dashboards showing combined server data can be created.

SQL Developer:

Master Data Services: To manage enterprise central database

Data-Tier Application: To map Database and Visual Studio builds

StreamInsight: Can analyse streaming data on the fly

Unicode Compression: New algorithm for Unicode storage

PowerPivot for SharePoint and Excel: Process datasets and reports.

Report Builder 3.0: Improved visualizations for SSRS

Parallel Data Warehouse: Data warehouses to be scaled over several physical SQL Servers.

SSMS enhancements for SQL Azure: SSMS support for cloud

 New Features Added in SQL Server 2012

SQL DBA:

Licensing Model: It’s not based on sockets introduced new core based licensing model

Edition changes: Introduced new BI edition and retired Datacenter, Workgroup and standard for small business.

AlwaysOn availability: Provides both Disaster Recovery and High Availability

Enhanced PowerShell Support: More CMDLETS introduced

Windows Server Core: Core is the GUI less version of Windows that uses DOS and PowerShell for user interaction SQL Server 2012 supports Windows Core

SQL Azure Enhancements: DB size limit increased to 150 GB, Azure data sync allows hybrid model etc.

SQL Developer:

Indirect checkpoints: Now we can configure checkpoint intervals database wise

ColumnStore-Index: Stores columns on page instead of rows

SSDT: BIDS is now SSDT SQL Server Data Tools.

FileTable: Builds upon FILESTREAM and SQL Server can access windows files on non-transactional

Sequence objects: an alternative for IDENTITY property

THROW: Improved error handling

New Conversion Functions: PARSE, TRY_PARSE, TRY_CONVERT

New Logical functions: CHOOSE, IIF

New String functions: CONCAT, FORMAT

New Date & Time functions: DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.

ROWS and RANGE: Support for Window framing on result sets

LAG and LEAD: To get the previous and next rows data

New Rank distribution functions: PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST

OFFSET / FETCH: Supports paging for ad hoc queries

FORCESCAN: New table hint

WITH RESULT SETS: More control on stored procedure returned result set metadata

sp_describe_first_result_set: Advanced version for SET FMTONLY option. Also DMV’s added for this

Statistical Semantic Search: Advanced feature builds upon the existing full-text search

Data Quality Services- DQS: A service added to MDS for advanced data profiling and cleansing

Power View: Light weight tool for BI reports

BI Semantic Model: Hybrid model that allows one data model will support all BI experiences

Big Data Support: ODBC driver for SQL Server that will run on a Linux platform etc.

Security:

User-Defined Server Roles: Customization for Server Roles

Database Audit: Like SQL Server Audit and it performs audits database level

Contained Databases: Users can be added on a database without login can easier migration

New Features Added in SQL Server 2014

SQL DBA:

Standard Edition Memory Capacity: Increased to 128 GB whereas 2012 standard editions it is 64 GB.

SQL Server Developer Edition is free: Microsoft made SQL Server 2014 Developer Edition license free.

AlwaysOn AG more secondaries:  2012 supports 4 whereas 2014 supports up to 8 secondaries

AlwaysOn AG readable secondaries: Readable secondaries remain online even though primary replica is down.

Add Azure Replica Wizard: Helps us to create asynchronous secondary replicas in Windows Azure

Buffer Pool Extension: SQL Server 2014 enable users to use Solid State Disk (SSD) to expand the SQL Server 2014 Buffer Pool as non-volatile RAM (NvRAM).

RG I/O control: In 2014 Resource Governor can also be configured to control the physical I/O.

Backup Encryption: SQL Server 2014 introduced native backup encryption. It supports several encryption algorithms AES 128, AES 192, AES 256, and Triple DES.

Managed Backup to Azure: SQL Server 2014 native backup supports Windows Azure with auto scheduling.

SQL Developer:

In-Memory OLTP Engine: We can enable memory optimization for selected tables and stored procedures.

Updateable Column-store Indexes: On SQL Server 2012 to utilize the column-store index, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction. In 2014 Column-Store Index must use all the columns in the table and can’t be combined with other indexes.

Cardinality Estimator Improvements:  Cardinality Estimator redesigned in SQL Server 2014.

Delayed Durability: Introduced delayed durable transactions. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

Partition Switching and Indexing: The individual partitions of partitioned tables can now be rebuilt.

Lock priority for Online Operations:  The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. We can also be able to configure terminating the blocking process related to rebuild.

Incremental Statistics: Now we can create partition level statistics by using the option INCREMENTAL

Inline Specification for Indexes: Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables. Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.

SELECT … INTO Enhancement: This statement now can be operated in parallel

Power View and Power BI: Power View now in 2014 supports OLAP cubes along with the tabular data. Power BI for office 365 is a cloud based BI solution.

Security:

SELECT ALL USER SECURABLES: A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.

CONNECT ANY DATABASE: A new server level permission when granted, a login can connect to any existing / future database in that instance. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process.

IMPERSONATE ANY LOGIN: A new server level permission when granted, allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases.

New Features Added in SQL Server 2016

SQL DBA:

SQL Server Developer Edition is free: Microsoft made SQL Server 2014 Developer Edition license free and same continued with SQL Server 2016 Developer Edition.

AlwaysOn Enhancements: Standard Edition will come with AGs support with one database per group synchronous or asynchronous, not readable (HA/DR only). 3 sync replicas supported whereas it was 2 in SQL 2014. Listener will be able to do round-robin load balancing of read-only requests on multiple secondaries. Now supports Microsoft DTC. SQL Server AlwaysOn to Azure Virtual Machine.

Database Scoped Configurations: The new ALTER DATABASE SCOPED CONFIGURATION allows us to control database level configurations. Ex: Parameter sniffing at database level.

Striped Backups to Microsoft Azure Blob Storage: In SQL Server 2016, SQL Server backup to URL using the Microsoft Azure Blob storage service now supports striped backups sets using block blobs with the maximum backup size of 12.8 TB.

File-Snapshot Backups to Microsoft Azure Blob Storage: In SQL Server 2016, SQL Server backup to URL now supports using Azure snapshots to backup databases in which all database files are stored using the Microsoft Azure Blob storage service.

Managed Backup: In SQL Server 2016 SQL Server Managed Backup to Microsoft Azure uses the new block blob storage for backup files. It supports automatic and custom scheduling, backups for system databases and backups for databases with simple recovery model.

No need to enable Trace flag 4199: Most of the query optimizer behaviours controlled by this trace flag are enabled unconditionally under the latest compatibility level (130)

TempDB enhancements: Trace Flags 1117 and 1118 are not required for tempdb anymore. When tempdb is having database files all files will grow at the same time based on growth settings. 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. We can have the control on tempdb configuration while installing SQL Server 2016.

New Default Database Size and Autogrow Values: For model database these default values are changed in 2016. Default data and log file size is 8 MB and auto-growth is 64 MB.

MAXDOP option for DBCC: Now we can specify MAXDOP option for DBCC CHECKTABLE, DBCC CHECKDB and DBCC CHECKFILEGROUP.

Replication Enhancements:  Replication supports memory-optimized tables and replication support enabled for Azure SQL Database.

SQL Developer:

ColumnStore Index Enhancements: A read-only nonclustered columnstore index is updateable after upgrade without rebuilding the index. Also columnstore indexes can be created for In-Memory tables

Live Query Statistics: Management Studio provides the ability to view the live execution plan of an active query.

Query Store: It can quickly find the 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. It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server.

Temporal Tables: SQL Server 2016 now supports system-versioned temporal tables. A temporal table is a new type of table that provides correct information about stored facts at any point in time.

Built-in JSON support: Java Script Object Notation (JSON) SQL Server 2016 enables ability to move JSON data to SQL Server tables using various clauses and functions. Ex: FOR JSON, OPENJSON, ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY

PolyBase: PolyBase allows you to use T-SQL statements to access data stored in Hadoop or Azure Blob Storage and query it in an ad-hoc fashion. It also lets you query semi-structured data and join the results with relational data sets stored in SQL Server. PolyBase is optimized for data warehousing workloads and intended for analytical query scenarios.

Stretch Database: SQL Server migrate historical data transparently and securely to the Microsoft Azure cloud. SQL Server handles the data movement in the background based on the policy we can configure. The entire table is always online and queryable.  Stretch Database doesn’t require any changes to existing queries or applications the location of the data is completely transparent to the application.

In-Memory OLTP Enhancements:  To be frank it’s not just enhancements. In-Memory OLTP on 2014 is just a trail version and in 2016 this is the first version where we have seen lot of things has been fixed and supported.

In-Memory – ALTER TABLE is log-optimized, and runs in parallel:  Only the metadata changes are written to the log also now it can run in parallel.

In-Memory Statistics: Now statistics are updated automatically

In-Memory Parallel Scan: Memory-optimized tables, and hash indexes, are now scannable in parallel.

In-Memory – LOBs with large row size:  Now memory optimized tables can have LOB type columns

In-Memory – TDE and MARS: MARS and TDE support enabled for In-Memory optimized tables

In-Memory T-SQL support: SQL Server 2016 overcomes the maximum limitations on 2014 In-Memory OLTP T-SQL Support.  Now In-Memory OLTP supports OUTPUT clause, UNIQUE index, FOREIGN KEY, Alter, Schema changes, Triggers, Check constraint, UNIION, UNION ALL, DISTINCT, OUTER JOIN, Sub queries and lot many native features supports in 2016 In-Memory optimized tables and natively compile stored procedures.

Foreign Key Relationship Limits: SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table from 253 to 10,000.

Support for UTF-8: BULK INSERT, bcp Utility and OPENROWSET now support the UTF-8 code page.

TRUNCATE TABLE – Partition:  The TRUNCATE TABLE statement now permits the truncation of specified partitions.

ALTER TABLE Enhanced: Now allows actions to be performed while the table remains available.

NO_PERFORMANCE_SPOOL: New query hint can prevent a spool operator from being added to query plans.

DROP IF:  Really useful feature. It drops the object if it exists in database.

sp_execute_external_script:  Advanced Analytics Extensions allow users to execute scripts written in a supported language such as R.

COMPRESS – DECOMPRESS:  Functions to convert values into and out of the GZIP algorithm.

New DATETIME:  DATEDIFF_BIG, AT TIME ZONE functions and sys.time_zone_info view are added to support date and time interactions.

STRING_SPLIT and STRING_ESCAPE: New string functions added

Security:

Row-Level Security: This is a predicate based access control.

Always Encrypted: Encrypt entire data (Data file, Log file, Backup, Communication Channel) and only application can see the data.

Dynamic Data Masking: Dynamic data masking limits sensitive data exposure by masking it to non-privileged users.

Transparent Data Encryption Enhanced: TDE supports Intel AES-NI hardware acceleration of encryption. This will reduce the CPU overhead of turning on Transparent Data.

AES Encryption for Endpoints: The default encryption for endpoints is changed from RC4 to AES.

New Credential Type: A credential can now be created at the database level in addition to the server level credential that was previously available.