Microsoft SQL Server SQL Server Evolution through different versions (2000 - 2016) SQL Server Version 2000 - 2016
Example
The following features added in SQL Server 2000 from its previous version:
- New data types were added (BIGINT, SQL_VARIANT, TABLE)
- Instead of and for Triggers were introduced as advancement to the DDL.
- Cascading referential integrity.
- XML support
- User defined functions and partition views.
- Indexed Views (Allowing index on views with computed columns).
The following features added in version 2005 from its previous version:
- Enhancement in TOP clause with “WITH TIES” option.
- Data Manipulation Commands (DML) and OUTPUT clause to get INSERTED and DELETED values
- The PIVOT and UNPIVOT operators.
- Exception Handling with TRY/CATCH block
- Ranking functions
- Common Table Expressions (CTE)
- Common Language Runtime (Integration of .NET languages to build objects like stored procedures, triggers, functions etc.)
- Service Broker (Handling message between a sender and receiver in a loosely coupled manner)
- Data Encryption (Native capabilities to support encryption of data stored in user defined databases)
- SMTP mail
- HTTP endpoints (Creation of endpoints using simple T-SQL statement exposing an object to be accessed over the internet)
- Multiple Active Result Sets (MARS).This allows a persistent database connection from a single client to have more than one active request per connection.
- SQL Server Integration Services (Will be used as a primary ETL (Extraction, Transformation and Loading) Tool
- Enhancements in Analysis Services and Reporting Services.
- Table and index partitioning. Allows partitioning of tables and indexes based on partition boundaries as specified by a PARTITION FUNCTION with individual partitions mapped to file groups via a PARTITION SCHEME.
The following features added in version 2008 from its previous version:
- Enhancement in existing DATE and TIME Data Types
- New functions like – SYSUTCDATETIME() and SYSDATETIMEOFFSET()
- Spare Columns – To save a significant amount of disk space.
- Large User Defined Types (up to 2 GB in size)
- Introduced a new feature to pass a table datatype into stored procedures and functions
- New MERGE command for INSERT, UPDATE and DELETE operations
- New HierarchyID datatype
- Spatial datatypes - To represent the physical location and shape of any geometric object.
- Faster queries and reporting with GROUPING SETS - An extension to the GROUP BY clause.
- Enhancement to FILESTREAM storage option
The following features added in version 2008 R2 from its previous version:
- PowerPivot – For processing large data sets.
- Report Builder 3.0
- Cloud ready
- StreamInsight
- Master Data Services
- SharePoint Integration
- DACPAC (Data-tier Application Component Packages)
- Enhancement in other features of SQL Server 2008
The following features added in version 2012 from its previous version:
- Column store indexes - reduces I/O and memory utilization on large queries.
- Pagination - pagination can be done by using “OFFSET” and “FETCH’ commands.
- Contained database – Great feature for periodic data migrations.
- AlwaysOn Availability Groups
- Windows Server Core Support
- User-Defined Server Roles
- Big Data Support
- PowerView
- SQL Azure Enhancements
- Tabular Model (SSAS)
- DQS Data quality services
- File Table - an enhancement to the FILESTREAM feature which was introduced in 2008.
- Enhancement in Error Handling including THROW statement
- Improvement to SQL Server Management Studio Debugging
a. SQL Server 2012 introduces more options to control breakpoints.
b. Improvements to debug-mode windows
c. Enhancement in IntelliSense - like Inserting Code Snippets.
The following features added in version 2014 from its previous version:
- In-Memory OLTP Engine – Improves performance up to 20 times.
- AlwaysOn Enhancements
- Buffer Pool Extension
- Hybrid Cloud Features
- Enhancement in Column store Indexes (like Updatable Column store Indexes)
- Query Handling Enhancements (like parallel SELECT INTO)
- Power BI for Office 365 Integration
- Delayed durability
- Enhancements for Database Backups
The following features added in version 2016 from its previous version:
- Always Encrypted - Always Encrypted is designed to protect data at rest or in motion.
- Real-time Operational Analytics
- PolyBase into SQL Server
- Native JSON Support
- Query Store
- Enhancements to AlwaysOn
- Enhanced In-Memory OLTP
- Multiple TempDB Database Files
- Stretch Database
- Row Level Security
- In-Memory Enhancements
T-SQL Enhancements or new additions in SQL Server 2016
-
TRUNCATE TABLE with PARTITION
-
DROP IF EXISTS
-
STRING_SPLIT and STRING_ESCAPE Functions
-
ALTER TABLE can now alter many columns while the table remains online, using WITH (ONLINE = ON | OFF).
-
MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
-
ALTER DATABASE SET AUTOGROW_SINGLE_FILE
-
ALTER DATABASE SET AUTOGROW_ALL_FILES
-
COMPRESS and DECOMPRESS Functions
-
FORMATMESSAGE Statement
-
2016 introduces 8 more properties with SERVERPROPERTY
a. InstanceDefaultDataPath
b. InstanceDefaultLogPath
c. ProductBuild
d. ProductBuildType
e. ProductMajorVersion
f. ProductMinorVersion
g. ProductUpdateLevel
h. ProductUpdateReference
