在 SQL Server 中，我应该为标识列创建索引，还是自动创建? [英] In SQL Server, should I create an index for an identity column, or is it created automatically?

问题描述

我相信当我创建一个 identity 列时，它会自动编入索引，但我不是 100% 确定.

我应该为 identity 列创建索引，还是自动创建?

解决方案

create table test (Id int identity)去sp_help 测试

<小时><前>对象test"没有任何索引，或者您没有权限.对象test"上没有定义约束，或者您没有权限.

作为一般做法，您会在标识列上创建唯一索引，这会加快查找速度.

通常您希望您的身份列也成为聚集索引"(Id int identity primary key 是快捷符号)，这意味着表在磁盘上的排列顺序与您的身份相同列是.这对插入进行了优化，因为被插入的页面往往在内存中.在某些情况下，当您非常频繁地对表中的其他数据进行范围查找时，您可以考虑将其他列聚集在一起，因为 SQL Server 只允许每个表一个聚集索引.

I believe when I create an identity column it gets indexed automatically, but I'm not 100% sure.

Should I create an index for an identity column, or is it created automatically?

解决方案

create table test (Id int identity)
go
sp_help test

---

The object 'test' does not have any indexes, or you do not have permissions.

No constraints are defined on object 'test', or you do not have permissions.

As a general practice you would create a unique index on your identity column, this speeds up lookups.

Usually you would like your identity columns to be 'clustered indexes' as well (Id int identity primary key is the shortcut notation), meaning table is layed out on disk in the same order your identity column is. This optimizes for inserts, as the page being inserted into tends to be in memory. In some cases, when you are doing ranged lookups very frequently on other data in the table, you may consider clustering other columns instead, as SQL Server only allows you one clustered index per table.

这篇关于在 SQL Server 中，我应该为标识列创建索引，还是自动创建?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！