MS SQL Server 2014和2016比較

礙於網路比較2014和2016的文章都寫的很零散,很少有將其整理起來的文章,因此就花了些時間來找尋相關的文章把他整理在這裡,網路上大神很多,因此幾乎每個講解我都有付上Source,可以進去看更Detail的細節。


The Difference between SQL Server 2014 and 2016 

Topic 2012 2014 2016
1.     SQL QUERY STORE   Y Y
2.     Polybase     Y
3.     Stretch Database     Y
4.     JSON Support     Y
5.     Row-Level Security     Y
6.     Always Encrypted     Y
7.     In-Memory Enhancements   Y Y
8.     Temporal data support     Y
9.     Dynamic Data Masking     Y
10.  Live Query Statistics     Y
11.  ColumnStore Index enhancements Y Y Y
12.  TempDB data files while installing SQL Server instance     Y
13.  The new IF EXISTS option with ALTER & DROP stmt     Y

 

 

  1. SQL QUERY STORE(Both 2014 and 2016)

Source: https://msdn.microsoft.com/en-us/library/dn817826(v=sql.120).aspx

query-store-01

The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed.

2016 2014
You cannot enable the query store for the master or tempdb database. You cannot enable the query store for the master  database.
SIZE_BASED_CLEANUP_MODE
Controls whether the cleanup process will be automatically activated when total amount of data gets close to maximum size. Can be AUTO (default) or OFF.
QUERY_CAPTURE_MODE
Designates if the Query Store captures all queries, or relevant queries based on execution count and resource consumption, or stops adding new queries and just tracks current queries. Can be ALL (capture all queries), AUTO (ignore infrequent and queries with insignificant compile and execution duration) or NONE (stop capturing new queries). The default value on SQL Server 2016 is ALL, while on Azure SQL Database is AUTO.
MAX_PLANS_PER_QUERY
An integer representing the maximum number of plans maintained for each query. The default value is 200.

Query Store lowers the bar dramatically for performance troubleshooting and allows you to perform several kinds of scenarios:
– Conduct system-wide or database-level analysis and troubleshooting
– Access the full history of query execution
– Quickly pinpoint the most expensive queries
– Get all queries whose performance regressed over time
– Easily force a better plan from history
– Safely conduct server restarts or upgrades
– Identify issues with upgrades

All data that query store stores will be available through the following Views:
– sys.query_store_query_text
– sys.query_store_query
– sys.query_store_plan
– sys.query_context_settings
– sys.query_store_runtime_stats_interval
– sys.query_store_runtime_stats

sql-server-2016-query-store

Source: https://sqlwithmanoj.com/2015/05/30/query-store-new-feature-in-sql-server-2016/

 

  1. Polybase (Only SQL 2016)

Source: https://msdn.microsoft.com/en-us/library/mt143171.aspx

ic852500

This feature will benefit you if your regular data processing involves dealing with a lot of large text files — they can be stored in Azure Blob Storage or Hadoop, and queried as if they were database tables.

PolyBase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server. It allows you to run queries on external data in Hadoop or Azure blob storage. The queries are optimized to push computation to Hadoop

 

  1. Stretch Database(Only SQL 2016)

Source: https://msdn.microsoft.com/en-us/library/dn935011.aspx

ic855114

The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly.

If you’re a decision maker If you’re a DBA
I have to keep transactional data for a long time. The size of my tables is getting out of control.
Sometimes I have to query the cold data. My users say that they want access to cold data, but they only rarely use it.
I have apps, including older apps, that I don’t want to update. I have to keep buying and adding more storage.
I want to find a way to save money on storage. I can’t backup or restore such large tables within the SLA.
  1. JSON Support(Only SQL 2016)

Source: https://msdn.microsoft.com/en-us/library/dn921897.aspx

Providing the ability to quickly move JSON data into tables. The way this is implemented in SQL 2016 is very similar to the way XML support is built in with FOR JSON and OPENJSON

ic835481

SQL Server provides built-in functions and operators that let you do the following things.

  • Parse JSON text and read or modify values.
  • Transform arrays of JSON objects into table format.
  • Use any Transact SQL query on the converted JSON objects.
  • Format the results of Transact-SQL queries in JSON format.

 

  1. Row-Level Security (Only SQL 2016)

Source: https://msdn.microsoft.com/en-us/library/dn765131.aspx

This restricts which users can view what data in a table, based on a function. SQL Server 2016 introduces this feature, which is very useful in multi-tenant environments where you may want to limit data access based on customer ID.

ic851773

Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context).

Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS enables you to implement restrictions on data row access. For example ensuring that workers can access only those data rows that are pertinent to their department, or restricting a customer’s data access to only the data relevant to their company.

The access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier. This makes your security system more reliable and robust by reducing the surface area of your security system.

 

  1. Always Encrypted (Only SQL 2016)

Source: https://msdn.microsoft.com/en-us/library/mt163865.aspx

ic854849

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results.

Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (SQL Database or SQL Server). As a result, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should have no access). By ensuring on-premises database administrators, cloud database operators, or other high-privileged, but unauthorized users, cannot access the encrypted data, Always Encrypted enables customers to confidently store sensitive data outside of their direct control. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff.

Task SSMS PowerShell T-SQL
Provisioning column master keys, column encryption keys and encrypted column encryption keys with their corresponding column master keys. Yes Yes No
Creating key metadata in the database. Yes Yes Yes
Creating new tables with encrypted columns Yes Yes Yes
Encrypting existing data in selected database columns Yes Yes No
  1. In-Memory Enhancements (Both 2014 and 2016)

Source: https://msdn.microsoft.com/en-us/library/dn133186.aspx

SQL Server 2014 introduced the concept of in-memory tables. These were optimally designed for high-speed loading of data with no locking issues or high-volume session state issues. While this feature sounded great on paper, there were a lot of limitations particularly around constraints and procedures. In SQL Server 2016, this feature is vastly improved, supporting foreign keys, check and unique constraints and parallelism. Additionally, tables up to 2TB are now supported (up from 256GB). Another part of in-memory is column store indexes, which are commonly used in data warehouse workloads. This feature was introduced in SQL 2012 and has been enhanced in each version since. In 2016 it receives some enhancements around sorting and better support with AlwaysOn Availability Groups.

sql-server-2016-in-memory

Source:

https://sqlwithmanoj.com/2015/06/14/in-memory-enhancements-and-improvements-in-sql-server-2016/

 

  1. Temporal data support (Only 2016)

Source: https://msdn.microsoft.com/en-us/library/mt604462.aspx

Depending on your scenario, you can either create new system-versioned temporal tables or modify existing ones by adding temporal attributes to the existing table schema. When the data in temporal table is modified, the system builds version history transparently to applications and end users. As a result, working with system-versioned temporal tables does not require any change to the way table is modified or how the latest (actual) state of the data is queried. In addition to regular DML and querying, temporal also provides convenient and easy ways to get insights from data history through extended Transact-SQL syntax. Every system-versioned table has a history table assigned but it is completely transparent for the users unless they want to optimize workload performance or storage footprint by creating additional indexes or choosing different storage options.

ic834265

With Temporal Data support you can:

  1. Time Travel back & forth and understand business trends over time.
  2. Track and Audit all data changes.
  3. Maintain a Slowly Changing Dimension (SCD) for decision support applications
  4. Recover from accidental data changes by repairing record-level corruptions.

sql-server-2016-temporal-12

sql-server-2016-temporal-061

Source: https://sqlwithmanoj.com/2015/06/15/temporal-data-support-in-sql-server-2016-part-1/

 

  1. Dynamic Data Masking (Only 2016)

Source: https://msdn.microsoft.com/en-us/library/mt130841.aspx

ic858838

Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. This feature helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a Policy-based Security feature that hides the sensitive data in the result set of a Query over designated database columns, while the data in the database is not changed.

 

  1. Live Query Statistics (Only 2016)

Source: https://msdn.microsoft.com/en-us/library/dn831878.aspx

https://sqlwithmanoj.com/2015/06/03/see-execution-plans-running-with-live-query-statistics-sql-server-2016/

SQL Server Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow from one query plan operator to another. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues. This feature is available beginning with SQL Server 2016 Management Studio, however it can work with SQL Server 2014.

The Live Query/Execution Plan will provide Real-time insights into the Query Execution process as the Control flows from one Operator to the another. It will display the overall Query Progress and Operator-level Run-time Execution Stats such as:

  1. Number of Rows processed
  2. Completion Estimate
  3. Operator progress – Elapsed time & Percentage done for each Operator
  4. Elapsed time & Percentage done for overall Query progress

sql-server-2016-live-query-stats1

ic752398

 

  1. ColumnStore Index enhancements (2012, 2014, 2016)

Source: https://sqlwithmanoj.com/2015/06/08/columnstore-indexes-evolution-from-sql-server-2012-2014-to-2016/

The full feature summary of ColumnStore Indexe evolution from SQL Server 2012 to 2016:

sql-server-2016-columnstoreindexes

 

  1. TempDB data files while installing SQL Server instance (Only 2016)

Source: https://sqlwithmanoj.com/2015/07/15/configure-multiple-tempdb-database-files-while-installing-sql-server-2016-new-feature/

SQL Server 2016 allows you to scale up your database with Enhanced Database Caching, using support for multiple TempDB files per instance for multi-core environments. This reduces metadata- and allocation contention for TempDB workloads, improving performance and scalability.While installing via UI the label besides the Input Control below mentions: “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.

sql-server-2016-install-06

sql-server-2016-ctp-2-4-setup-02

sql-server-2016-install-07

 

  1. The new IF EXISTS option with ALTER & DROP stmt (Only 2016)

Source:https://sqlwithmanoj.com/2015/11/03/new-syntax-option-if-exists-with-drop-and-alter-statements-in-sql-server-2016/

The new IF EXISTS syntax can be used optionally to check & drop items form a single statement with these DDLs:

  1. ALTER: statement with DROP COLUMN & DROP CONSTRAINT option.
  1. DROP: statement with all Database objects, like: AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW.

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s