SQL Server 创建索引的限制

最新推荐文章于 2024-07-30 08:00:00 发布

武哥博文

最新推荐文章于 2024-07-30 08:00:00 发布

阅读量2.5k

点赞数

分类专栏： 001-B 数据库管理文章标签： sql server table include insert warnings less

本文链接：https://blog.csdn.net/GRANDTREE/article/details/4058910

版权

在SQL Server中创建索引时，遇到900字节的键长度限制。当包含多个或大尺寸列时，需确保不超过最大索引键大小。警告提示可能在插入/更新操作时因键值超过900字节而失败。可以考虑使用非聚集索引的INCLUDE子句来包含非键列，以避免键列大小限制。

摘要由CSDN通过智能技术生成

今天准备在SQL Server2005的一个表的字段上间索引，结果创建失败。检查一下表结构，原来该字段是NVarchar(Max)，不能建索引。

后来将该字段改成nvarchar(4000)，重新执行创建语句，出现如下提示：

Warning! The maximum key length is 900 bytes. The index 'IX_PROJECT_LABEL' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

查询MSDN，

解释如下：

Maximum Size of Index Keys

When you design an index that contains many key columns, or large-size columns, calculate the size of the index key to make sure that you do not exceed the maximum index key size. SQL Server retains the 900-byte limit for the maximum total size of all index key columns. This excludes nonkey columns that are included in the definition of nonclustered indexes.

Calculating the Size of an Index Key

To calculate the size of an index key, follow these steps.

Display the properties of the table columns on which the index will be based. You can do this by using the sys.columns catalog view.
Sum the length of each column that will be defined in the index key.
For example, the following statement aggregates the max_length column of the sys.columns catalog view for the spe