在SQL Server中，我应该为标识列创建索引，还是自动创建？ [英] In SQL Server, should I create an index for an identity column, or is it created automatically?
问题描述
我相信当我创建一个身份列时，它会自动编入索引，但我不是100％肯定。
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.
通常您希望您的标识列也是聚簇索引（ Id int identity primary key is is快捷符号），意义表按照您的标识列的顺序放在磁盘上。这样可以优化插入，因为插入的页面往往位于内存中。在某些情况下，当您对表中的其他数据进行远程查找时，可以考虑对其他列进行集群，因为SQL Server只允许每个表使用一个聚簇索引。
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屋！
