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.
