哈希分区和顺序分区_SQL Server中的哈希分区

哈希分区和顺序分区

In SQL Server, when talking about table partitions, basically, SQL Server doesn’t directly support hash partitions. It has an own logically built function using persisted computed columns for distributing data across horizontal partitions called a Hash partition.

在SQL Server中，当谈到表分区时，基本上，SQL Server不直接支持哈希分区。 它具有自己的逻辑构建函数，该函数使用持久性计算列在跨Hash分区的水平分区中分配数据。

For managing data in tables in terms storage, performance or maintenance, there is a need to keep data allocation units in large chunks. For the purpose of speeding up loading and archiving of data, tables may need to be divided into multiple units that can reside in multiple filegroups. When referring to partitions, there are two types; Vertical and Horizontal partitions.

为了在存储，性能或维护方面管理表中的数据，需要将数据分配单元保持在大块中。 为了加快数据的加载和归档，可能需要将表分为多个单元，这些单元可以驻留在多个文件组中。 当提到分区时，有两种类型；一种是分区。 垂直和水平隔板 。

Note: This concept was explained nicely by Milica Medic in the article Database table partitioning in SQL Server.

注意： Milica Medic 在“ SQL Server 中的 数据库表分区”一 文中很好地解释了此概念 。

First, I will describe partitions at a high level.

首先，我将在较高级别上描述分区。

垂直分区 (Vertical partitions)

Generally, Vertical partitioning means a semi data normalization process based on data use vs meaning within the database design. It involves removing redundant columns from the table and organizing a second table connected via a relationship. Suppose, we have a “heavy” table in terms of size; large columns may exist in the tables for an example, nvarchar, xml etc. Data might be rarely accessed in those large columns. When performing an operation in heavy tables, issue may arise with related IO cost, locking, latch contention etc. Vertical portioning is meant to address this by offloading lesser used columns and their resulting data to another table.

通常，垂直分区意味着基于数据库设计中数据使用与含义的半数据规范化过程。 它涉及从表中删除多余的列，并组织通过关系连接的第二个表。 假设我们在大小上有一个“沉重的”表； 例如，表中可能存在大列，例如nvarchar，xml等。在那些大列中可能很少访问数据。 在繁重的表中执行操作时，可能会产生相关的IO成本，锁定，锁存器争用等问题。垂直分配旨在通过将较少使用的列及其产生的数据卸载到另一个表中来解决此问题。

Vertical partitioning should be considered carefully, though, because it might affect performance if the partition is very large because there may be an increased number of joins in queries used for getting the records, when all of the columns (vs just a sub-set) are required.

但是，应该仔细考虑垂直分区，因为如果分区很大，则垂直分区可能会影响性能，因为当所有列（相对于子集）使用时，用于获取记录的查询中的联接数量可能会增加。是必须的。

水平隔板 (Horizontal partitions)

In this approach, the tables are divided into multiple tables, but with each table having the same columns. The partitioning, in this case, is that the rows are divided, amongst these tables, as per applied rules.

在这种方法中，表被分为多个表，但是每个表具有相同的列。 在这种情况下，分区是根据应用的规则在这些表之间划分行。

SQL Server中的哈希分区 (Hash partitions in SQL Server)

In simple terms, a Hash partition is a Horizontal partition. It’s needed to logically build a hashing function and with it, ensure data is randomly distributed across the partition. This means that the partition key functions as an identifier and any new row are allocated to a particular partition based on the result of passing the partition key into a hashing algorithm. Due to this, data will be inserted randomly across the partition. Compared with a Horizontal Partition the only difference is that, here there is a need to add persisted