Shulou(Shulou.com)06/01 Report--

The editor will share with you what are the differences between different versions of sql server. I hope you will gain a lot after reading this article. Let's discuss it together.

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

Comparison of different versions of SQL SERVER

SQL SERVER 2000

Log shipping, indexed view

SQL SERVER 2005

Zoning

Database mirroring

Online index

Database snapshot

Copy

Failover cluster

SQL SERVER 2008

Data compression

Resource governor

Backup compression

SQL SERVER 2008 R2

The R 2 flag indicates that this is an intermediate version of SQL Server, not a major version. At present, this version is still widely used by my customers with stable performance, although there are few new features:

New data center version, supporting up to 256 cores.

Unicode compression

(provides a simple compression scheme for Unicode storage. Through Unicode compression, the space consumption of Unicode characters can be reduced.)

SQL SERVER 2012

AlwaysOn

Columnstore index

Enhanced audit featur

Big data supports

SQL SERVER 2014

This version of the new special effects are very many, and very useful, you can learn more.

Memory optimization table

Backup encryption

A new design for cardinality estimation

AlwaysOn enhancements

Delayed persistence (some or all transactions are designated as deferred persistent transactions, thus reducing latency)

Partition switching and index generation

Column storage index

Buffer pool expansion is the use of SSD to extend buffer pools

Incremental statistics

Resource governor enhancements

(- previously, only CPU and memory can be controlled, and IO can be controlled from 2014)

DBCC CHECK supports maxdop prompts

SQL SERVER 2016

Whole process encryption technology (Always Encrypted)

JSON support

Multiple TempDB database files

(it was also supported in the past, starting in 2014 at the error log prompt, and in 2016, it can be set at installation time. )

Query Store

(as mentioned in the Microsoft training a few days ago, it's a pretty good function. It can help solve the problem of parameter sniffing, and it can also be used when upgrading the database)

Support for R language

Live Quer y St at ist ics

(you can see the cost of implementing the plan more clearly (water flow))

SQL SERVER 2017

Recoverable online index reconstruction

Allows you to resume an online index rebuild operation after a failure, such as a failover to a replica or insufficient disk space.

IDENTITY_CACHE option

When this option is set to OFF, it avoids identifying gaps in column values if the server restarts unexpectedly or fails over to the secondary server

CLR uses Code access Security (CAS) in .NET Framework, and the framework no longer supports security boundaries.

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

Chart database function

For many-to-many relationship modeling

Read-scale availability groups without cluster

You can build a read availability group without relying on the cluster to share the read pressure. However, high availability cannot be achieved at this time.

R/PYTHON machine learning function

After reading this article, I believe you have a certain understanding of the differences between different versions of sql server. You want to know more about it. Welcome to follow the industry information channel. Thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.