Introduction
This article will explain the main features in SQL Server 2017, 2016, 2015, 2014, 2012, 2008, 2005, 2000, 7, 6.5, 6.0, 4.2, 1.1 and 1.0.
In the past, the first SQL Server versions supported OS/2 (an operative system created by Microsoft and IBM) and Windows.
Now, the new versions of SQL Server (vNext and SQL Server 2017) can be installed in Linux. 15 years ago, it was impossible to think that. Linux and Microsoft were just like oil in water and now, Microsoft loves Linux.
Also, we now enjoy full integration with Azure, Tabular Databases, SSIS, SSAS and more. In this article, we will talk about all these changes and improvements.
Let’s take a look at the features of each version.
SQL Server 2017
Year: 2017
I think that the main feature is that now SQL Server can be installed in Linux and Docker, which is huge. There is no universal installer. In fact, the installers for Red Hat, Ubuntu and SUSE are different, but it is a huge step to support Linux.
In this version, CLR assemblies can now be added to a white list.
SQL Server 2017 includes Python support, as well as …
- Identity Cache,
- Graph Database,
- Resumable Indexes,
- New string functions,
- Adaptative Query Processing,
- Automatic tuning,
- New dynamic Management views (DMV) like the sys.dm_db_log_stats, sys.dm_tran_version_store_space_usage and sys.dm_db_log_info.
SSIS is now supported in Linux. In SSDT, you have now a DAX editor and tabular databases are the default installation.
In SSRS, you can run native DAX queries. R Services changed the name to Machine Learning Services and access to Python libraries.
For a complete list of changes in SQL Server 2017, refer to this link:
SQL Server 2016
Code Name: Helsinki
Year: 2016
In SQL Server 2016, PolyBase support was introduced. With PolyBase, you can query NoSQL data like csv files stored in Azure Blob Storage or in HDInsight.
Another interesting feature is JSON support which includes new features to handle JSON data.
Stretch database is another feature that allows to archive data in Azure SQL.
It is also possible to use temporal tables to save data changes.
SQL Server Management Studio is separated from the database engine. It includes its own versions.
Always encrypted is introduced as a new feature that allows to increase your security.
Dynamic Data Masking helps you to protect sensitive data. Also, you can apply now Row Level Security.
SSRS now supports a web Report Portal and a Mobile Report Publisher. SSIS now supports Always On Availability Groups.
For a complete list of changes, refer to this link:
SQL Server 2014
Code Name: Hekaton
Year: 2014
The main features in SQL Server 2014 were the integration to Azure and Memory Optimized Tables. You can for example migrate your Database on-premises to a Virtual Machine in Azure. It is possible to migrate a database backup to Azure.
Your Data files can be stored in Azure. There are enhancements in AlwaysOn, Columnstore Indexes.
You also have Power View reports for multidimensional models.
For a complete list of new features, refer to this link:
SQL Server 2012
Code Name: Denalli
Year: 2012
In this new version, the BIDS (Business Intelligence Development Studio) was replaced by SSDT (SQL Server Data Tools). This SQL Server version can be installed in Windows Core Edition, a secure operative system. Business Intelligence edition for SQL Server was introduced; An edition that supports the most important features related to Business intelligence.
Another big feature is the AlwasyOn SQL Server Failover Cluster Instances, AlwaysOn Availability Groups.
SSMS includes IntelliSense features and a Database Recovery Advisor.
FileTable support to handle system files in SQL Server. The semantic search is a feature included in Full text search to search in unstructured documents.
Columnstore Indexes is another powerful feature to get faster query responses in tables like Data Warehouse tables. Online index creation is now supported.
In Business Intelligence, the support of Tabular Models and the DAX functions was a very significative change.
The same the support of Power Pivot.
For more information about all the features in SQL Server 2012, refer to this link:
SQL Server 2008
Code Name: Katmai
Year: 2008
Another important feature was the CDC (Change Data Capture) to track changes in a table.
Data Collector was a feature to collect monitoring information in a Data Warehouse.
SQL 2008 also offered compressed indexes and tables.
In data types, it was introduced the hierarchyid data type and spatial data types. Also, some new date and time data types and User-Defined data types.
You can also implement in this version filtered indexes and statistics. Another big feature was the Transparent Data Encryption and some encryption functions like the CRYPT_GEN_RANDOM and KEY_NAME.
For more information about SQL Server 2008, refer to this link:
SQL Server 2005
Code Name: Yukon
Year: 2005
This version introduced the SSMS that we use now. The BIDS (Business Intelligence Development Studio) was created in this version. SQL Server included the Adventureworks database in the installer.
This version introduced the Service Broker to handle message queue.
SSIS was introduced. In the past, it was used the Data Transformation Services with a different technology used which was completely changed.
The Reporting Services and Analysis services used a completely new platform.
This version introduced the XML enhancements, CLR support, Failover Cluster Enhancements, Database Mirroring, Database Snapshots.
For more information about SQL Server 2005, refer to this link:
SQL Server 2000
Code Name: Shiloh
Year: 2000
SQL Server 2000 was an important version where the OLAP Services in SQL 7 was replaced by the Analysis Services.
It was also introduced the XML support, User Defined Functions, Indexed Views, Replication enhancements, Log Shipping and more.
For more information about SQL Server 2000, you can download the documentation from this link and check the What is new in Microsoft SQL Server 2000:
SQL Server 7.0
Code Name: Sphinx
Year: 1998
This version included tools for query analysis like the Query Analyzer (which was replaced by the SSMS in SQL 2005).
It introduced OLAP Services (SSAS now) and Data Transformation Services (SSIS now).
This code base was now fully independent from Sybase.
Nice wizards to create tables, indexes, stored procedures, replication and schedule backups.
It was also introduced SQL Profiler, which is still in the last versions but will be removed soon.
For more information about SQL Server 7, refer to these links:
SQL Server 6.5
Code Name: Hydra
Year: 1996
This version was ANSI Standard Compliant, and it was based on Sybase codebase.
SQL Server 6.0
Code Name: SQL95
Year: 1995
In this version, SQL Server was an important database in the market. This version was supported in Windows 95. The main feature in this version was the replication.
SQL Server 4.2
Code Name: SQLNT
Year: 1993
Yes, there was no version 2 or 3. Why? Some experts think that the Microsoft guys do not know how to count and that is why there was no Windows 9 ?
This version started to support Windows NT. SQL Server 4.2 was supported in Windows NT and OS/2 (an IBM-Microsoft OS).
SQL Server 1.1
Code Name: Pietro
Year: 1991
SQL Server 1.0
Code Name: Filipi
Year: 1989
In this year, Microsoft joined Sybase. They granted permission to access the code and create a new database. Sybase created the code for Microsoft. The also joined Ashton/Tate, the creators of dBASE, to help in the marketing. There are still some stored procedures with the same functionality than Sybase.
It was a nice database with networking support. It used the SAF (SQL Server Administrator Facility) to create databases, set parameters and run SQL queries. It had no documentation.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023