SQLServer2016之后增加了索引列数的限制从16个列增加到了32个列

悠悠 2021-06-26 16:06 312阅读 0赞

## 创建带有包含列的索引 ##

[https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017][https_docs.microsoft.com_zh-cn_sql_relational-databases_indexes_create-indexes-with-included-columns_view_sql-server-2017]

本主题说明如何通过使用 `SQL Server` 或 `SQL Server Management Studio` ，添加包含列（或非键列）以便在 `Transact-SQL`中扩展非聚集索引的功能。 通过包含非键列，可以创建覆盖更多查询的非聚集索引。 这是因为非键列具有下列优点：

*  它们可以是不允许作为索引键列的数据类型。
 *  在计算索引键列数或索引键大小时， 数据库引擎 不考虑它们。

当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。 这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 `I/O` 操作。

> 备注
> 
> 当索引包含查询引用的所有列时，它通常称为“覆盖查询”。

## 始之前 ##

### 设计建议 ###

*  重新设计索引键大小较大的非聚集索引，以便只有用于搜索和查找的列为键列。 使覆盖查询的所有其他列成为非键列。这样，将具有覆盖查询所需的所有列，但索引键本身较小，而且效率高。
 *  将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 `32`，最大索引键大小为 1,700 字节，而在 `SQL Server 2016 (13.x)` 以前，最大键列数为 16，最大索引键大小为 900 字节）。 数据库引擎计算索引键列数或索引键大小时，不考虑非键列。

### 限制和局限 ###

*  只能对非聚集索引定义非键列。
 *  除了 `text`、 `ntext`和 `image` 之外的所有数据类型都可以用作非键列。
 *  精确或不精确的确定性计算列都可以是非键列。 有关详细信息，请参阅 计算列上的索引。
 *  只要允许将计算列数据类型作为非键索引列，从 `image`、 `ntext`和 `text` 数据类型派生的计算列就可以作为非键索引列。
 *  除非先删除某一表的索引，否则无法从该表中删除非键列。
 *  除进行下列更改外，不能对非键列进行其他更改：
    
     *  将列的为空性从 `NOT NULL` 改为 `NULL`。
     *  增加 `varchar`、 `nvarchar`或 `varbinary` 列的长度。

### 安全性 ###

**Permissions**

要求对表或视图具有 `ALTER` 权限。 用户必须是 `sysadmin` 固定服务器角色的成员，或者是 `db_ddladmin` 和 `db_owner` 固定数据库角色的成员。

## 使用 `SQL Server Management Studio` ##

### 创建带有非键列的索引 ###

1.  在对象资源管理器中，单击加号以便展开包含您要创建带有非键列的索引的表的数据库。
2.  单击加号以便展开 “表” 文件夹。
3.  单击加号以便展开您要创建带有非键列的索引的表。
4.  右键单击“索引”文件夹，指向“新建索引”，然后选择“非群集索引…”。
5.  在 “新建索引” 对话框的 “常规” 页中，在 “索引名称” 框中输入新索引的名称。
6.  在“索引键列”选项卡下，单击“添加…”。
7.  在“从 table\_name 中选择列”对话框中，选中要添加到索引的一个或多个表列的复选框。
8.  单击“确定” 。
9.  在“包含性列”选项卡下，单击“添加…”。
10. 在“从 table\_name 中选择列”对话框中，选中要作为非键列添加到索引的一个或多个表列的复选框。
11. 单击“确定” 。
12. 在 “新建索引” 对话框中，单击 “确定”。

## 使用 Transact-SQL ##

### 创建带有非键列的索引 ###

1.  在 “对象资源管理器” 中，连接到 数据库引擎的实例。
2.  在标准菜单栏上，单击 “新建查询”。
3.  将以下示例复制并粘贴到查询窗口中，然后单击“执行” 。

USE AdventureWorks2012;  
    GO  
    -- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns. 
    -- index key column is PostalCode and the nonkey columns are 
    -- AddressLine1, AddressLine2, City, and StateProvinceID. 
    CREATE NONCLUSTERED INDEX IX_Address_PostalCode  
    ON Person.Address (PostalCode)  
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);  
    GO

[https_docs.microsoft.com_zh-cn_sql_relational-databases_indexes_create-indexes-with-included-columns_view_sql-server-2017]: https://docs.microsoft.com/zh-cn/sql/relational-databases/indexes/create-indexes-with-included-columns?view=sql-server-2017