My Picks: Top New Features in SQL Server 2005, 2008/R2, 2012, 2014, and 2016

1.  SQL Server 2005

1)    The Max data type

2)    CLR Integration

3)    XML Support

4)    New Error Handing – TRY…CATCH

5)    DMVs and DMFs

6)    Ranking Functions

7)    CTE

8)    NEW Operators: Except/Intercept, APPLY, PIVOT/UNPIVOT

9)    Separation of Schema and Users

10) Data Encryption (column-level)

11) Data Compression

12) Index Rebuild and Reorganization

13) DDL Triggers

14) Database Snapshot

15) Database Mirroring

16) SSMS replaces Query Analyzer and Enterprise Manager

17) A Unified BI Development Tool - BIDS replaces Enterprise Manager for DTS packages, Analysis Manager for Analysis Projects and Visual Studio 2003 .NET for Reporting Services

18) Full-text Search is managed by SQL Server now

19) Service Broker

20) SSIS replaces DTS

21) SQL Server 2005 SP2 integrate SSRS with SharePoint


2.  SQL Server 2008

1)    Slipstream Installation

2)    SQL Server Audit

3)    Data Collector - With Performance Data Collector in SQL Server 2008, you can now store performance data from a number of SQL Servers in one central location.

4)    Backup compression

5)    Change Track and Data Change Capture

6)    Resource Governor

7)    Extended Events

8)    Policy-based Management

9)    Intellisense and T-SQL Debugger

10) Filestream storage

11) Sparse columns and Column Sets

12) New Data and Time Types (DATE, TIME, DATETIMEOFFSET and DATETIME2)

13) Spatial Data Types

14) HierarchyID

15) Grouping Sets and Grouping_ID [the Grouping() function is not new. It has been in since SQL Server 2000 at least]

16) Merge

17) Table-valued parameters

18) Filtered index and statistics

19) Transparent data encryption

20) Peer-to-Peer Transactional Replication

21) SQL Server Utility (in SP2)

22) SSIS Scripting Environment has changed to Microsoft Visual Studio Tools for Applications (VSTA) from Microsoft Visual Studio for Applications (VSA) in SQL Server 2005 to support C#

23) New Data Profiling Task and Data Profile Viewer

24) IIS is not needed for SSRS 2008

25) Gauge and Tablix Data Regions

26) Rendering to Microsoft Word

27) Report Builder 2.0

3.  SQL Server 2008 R2

1)    PowerPivot for SharePoint and PowerPivot for Excel (and PivotTable and PivotReport Reports)

2)    Master Data Services

3)    SQL Server Installation – SysPrep (for preparing image)

4)    Maps, Sparklines, Data bars, and Indicators in SSRS

5)    Report Parts in SSRS

6)    Shared Data Sources and Shared Datasets

7)    Report Builder 3.0

8)    Microsoft SQL Azure

9)    Integration of Analysis Services with SharePoint - Analysis Services can be hosted within a SharePoint farm.

10) PowerPivot for Excel 2010 for building multidimensional data sets and Data Analysis Expressions (DAX)


4.  SQL Server 2012

1)    Server Core Installation

2)    Data Quality Services

3)    SSDT replaces BIDS

4)    AlwaysOn

5)    Contained Database

6)    FileTables

7)    Statistical Semantic Search

8)    Sequence Objects

9)    THROW

10) 14 New Functions and the Changed Log Function

(1)  PARSE (Transact-SQL)

(2)  TRY_CONVERT (Transact-SQL)

(3)  TRY_PARSE (Transact-SQL)

(4)  DATEFROMPARTS (Transact-SQL)

(5)  DATETIME2FROMPARTS (Transact-SQL)

(6)  DATETIMEFROMPARTS (Transact-SQL)

(7)  DATETIMEOFFSETFROMPARTS (Transact-SQL)

(8)  EOMONTH (Transact-SQL)

(9)  SMALLDATETIMEFROMPARTS

(10)        TIMEFROMPARTS (Transact-SQL)

(11)        CHOOSE (Transact-SQL)

(12)        IIF (Transact-SQL)

(13)        CONCAT (Transact-SQL)

(14)        FORMAT (Transact-SQL)

(15)        LOG float_expression [, base ] )

11) Eight Analytic Functions

(1)  FIRST_VALUE (Transact-SQL)

(2)  LAST_VALUE (Transact-SQL)

(3)  LEAD (Transact-SQL)

(4)  LAG (Transact-SQL)

(5)  PERCENT_RANK (Transact-SQL)

(6)  PERCENTILE_CONT (Transact-SQL)

(7)  PERCENTILE_DISC (Transact-SQL)

(8)  CUME_DIST (Transact-SQL)

12) SQL Server Express LocalDB replaces SQL Server Express

13) Columnstore index

14) Master Data Services Add-in for Excel

15) Project Connection Managers

16) SSIS Parameters

17) SSISDB Catalog

18) Tabular Modeling (and VertiPag) and BISM

19) Power View - a feature of SQL Server 2012 Reporting Services Add-in for Microsoft SharePoint Server 2010 Enterprise Edition

20) Data Alert (SSRS)

21) SQL Server Backup and Restore with Windows Azure Blob Storage Service

5.  SQL Server 2014

1)    In-Memory OLTP (A memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.) and Memory Optimization Advisor - help you quickly analyze your tables and walks you through reviewing and migrating disk-based table to In-Memory OLTP tables.

2)    Row-level security

3)    Enhancements

(1)    Windows Azure Integration - use Windows Azure for offsite backup storage and as a disaster recovery site. 

(2)    New Design for Cardinality Estimation

(3)    Delayed Durability - A delayed durable transaction returns control to the client before the transaction log record is written to disk.

(4)    Managing the Lock Priority of 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.

(5)    Clustered columnstore indexes - Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries.

(6)    Buffer Pool Extension - The Buffer Pool Extension provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.

(7)    Incremental Statistics - CREATE STATISTICS and related statistic statements now permits per partition statistics to be created by using the INCREMENTAL option.

(8)    Resource Governor Enhancements for Physical IO Control - The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool.

(9)    Power View for Multidimensional Models (in SharePoint mode).

(10) Support for the Google Chrome browser. 


6.  SQL Server 2016

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

2)    Query Store or Query Data Store

3)    Temporal Tables

4)    Format Query Results as JSON with FOR JSON

5)    Stretch Database

6)    Dynamic data masking

7)    Polybase Support

PolyBase allows you to use T-SQL statements to access data stored in Hadoop or Azure Blob Storage and query it in an adhoc 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.

8)    Enhancements

(1)    Updateable nonclustered columnstore indexes

(2)    Backup to Microsoft Azure now supports using block blobs instead of page blobs

(3)    Multiple TempDB Database Files

(4)    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.

(5)    TRUNCATE TABLE - now permits the truncation of specified partitions.

(6)    AlwaysOn Support for SSISDB Databases

(7)    Parallel processing for multiple table partitions in Tabular models

(8)    DBCC XMLA - checks for physical and logical data corruption in both tabular and multidimensional databases

(9)    New DAX Functions for Tabular models

(10) Enable and disable subscriptions - New user interface options