Comparison of functions between different versions of SQLSERVER.
I have gone through so many versions since I first came into contact with SQL SERVER 2000. Below is a brief explanation of the newly added features in each version:
SQL SERVER 2000 Log shipping Index View.
SQL SERVER 2005
partition.
Database mirroring
(Only SQL Server 2005 Enterprise Edition SP1 and higher versions support asynchronous database mirroring.)
Online indexing
Database snapshot
Copy
Failover Cluster.
SQL SERVER 2008.
Data compression Resource Governor Backup compression.
SQL SERVER 2008 R2.
The R2 flag indicates that this is an intermediate version of SQL Server, not a major version. Currently, this version is still widely used in my clients and has stable performance, although there are relatively few new features added:
Newly added data center version, supporting up to 256 cores
Unicode compression.
(Provide a simple compression scheme for Unicode storage, which can reduce the space occupied by Unicode characters).
SQL SERVER 2012.
AlwaysOn
Columnstore index
Enhanced audit functionality
Big data support.
SQL SERVER 2014
This version has a lot of new special effects and is very useful. You can learn more about it.
Memory optimization table
Backup encryption
New design for cardinality estimation
AlwaysOn Enhancements
Delayed Persistence (designating some or all transactions as delayed persistent transactions to shorten latency)
Partition switching and index generation.
(The official website states' Partition Switching and Indexing ', but there seems to be an issue. In fact, a single partition of the partition table can be rebuilt.)
Column storage index
Buffer pool extension is the use of SSD to extend the buffer pool
Incremental statistical information
Resource Governor Enhancements
(Previously, only CPU and memory could be controlled, but since 2014, IO can be controlled).
DBCC CHECK supports maxdop prompt.
SQL SERVER 2016.
Always Encrypted
JSON support
Multiple TempDB database files.
(Previously, it was also supported. Since 2014, it has been prompted in the error log. In 2016, it can be set during installation.)
Query Store
(I attended Microsoft's training a few days ago and also talked about it. It's a pretty good feature. It can help solve the problem of parameter sniffing, and can also be used when upgrading databases.)
Supports R language
Live Quer y St at ist ics
(You can see the cost of executing the plan more clearly (water flow)).
SQL SERVER 2017
Recoverable online index reconstruction.
Allow you to resume online index rebuild operations after a failure occurs, such as failing over to a replica or running out of disk space.
Identity_CACHE option.
When this option is set to OFF, it can avoid identifying gaps in column values in the event of an unexpected server restart or failure to switch to a secondary server.
CLR in NET Framework uses Code Access Security (CAS), which no longer supports security boundaries.
Using PermISSION_SET= The CLR assembly created by SAFE may be able to access external system resources, call unmanaged code, and obtain sysadmin permissions.
Chart database functionality.
Used for modeling many to many relationships.
Read scale availability groups without cluster.
It is possible to set up an availability group for reading without relying on the cluster, to share the reading pressure. However, high availability cannot be achieved at this time.
R/PYTHON machine learning features.