What are the differences between various versions of sql server?

SQL SERVER2000 version focuses on log shipping, indexed views; 2005 version focuses on partitioning, database mirroring, etc.; 2008 version focuses on data compression, resource governor, etc.; 2012 version has enhanced auditing functions, etc.

Comparison of various versions of SQL SERVER

1. SQL SERVER 2000

Log shipping, indexed view

2. SQL SERVER 2005

Partition

Database Mirror

Online Index

Database Snapshot

Replication

Failover Cluster

3, SQL SERVER 2008

Data Compression

Resource Governor

Backup compression

4, SQL SERVER 2008 R2

The R2 flag indicates that this is an intermediate version of SQL Server, not a major version. This version is currently used by my customers , it is still widely used and has stable performance, although there are relatively few new features:

New data center version supports up to 256 cores.

Unicode compression

(for Unicode storage provides a simple compression solution. Through Unicode compression, the space occupied by Unicode characters can be reduced)

5, SQL SERVER 2012

AlwaysOn

Columnstore index

Enhanced audit function

Big data support

6, SQL SERVER 2014

New in this version There are many special effects and very useful, you can learn more about them.

Memory optimization table

Backup encryption

New design for cardinality estimation

AlwaysOn enhancement Features

Delayed persistence (designate some or all transactions as delayed durable transactions, thereby reducing latency)

Partition switching and index generation

Column store index

Buffer pool expansion is to use SSD to expand the buffer pool

Incremental statistics

Resource governor enhancements

(--Previously, only CPU and memory could be controlled, IO can be controlled starting from 2014)

DBCC CHECK supports maxdop prompt

7, SQL SERVER 2016

Full encryption technology (Always Encrypted)

JSON supports

Multiple TempDB database files

(It was also supported before. In 2014, there was an error log prompt. In 2016, it can be set during installation. )

Query Store

(I mentioned it in a Microsoft training a few days ago. It’s a pretty good feature. It can help solve the problem of parameter sniffing and can also be used when upgrading the database. to it)

Support R language

Live Quer y St at ist ics

(You can see the overhead of the execution plan more clearly (water flow type))

8, SQL SERVER 2017

Recoverable online index rebuild

Allows you to recover after a failure (such as failover to a replica or insufficient disk space) Online index rebuild operations.

IDENTITY_CACHE option

When this option is set to OFF, it avoids gaps in identity column values ​​in the event of an unexpected server restart or failover to a secondary server

CLR uses Code Access Security (CAS) in the .NET Framework, which no longer supports security boundaries.

CLR assemblies created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code and obtain sysadmin privileges

Chart database capabilities

Used for many-to-many Relational modeling

Read-scale availability groups without cluster

You can build read-scale availability groups to share read pressure without relying on clusters. However, high availability cannot be achieved at this time.

R/PYTHON machine learning functions

The above is the detailed content of What are the differences between various versions of sql server?. For more information, please follow other related articles on the PHP Chinese website!