外键和索引问题 [英] foreign key and index issue

问题描述

我正在使用SQL Server 2008 Enterprise。我有一个表，其中一列是引用另一个表（在同一个数据库中的另一个列）作为外键，这里是相关的SQL语句，更详细地说，表[Foo]中的列[AnotherID]是指另一个表[Goo]的列[GID]作为外键。 [GID]是表[Goo]上的主键和聚簇索引。

我的问题是，如果我不在[AnotherID]列上创建索引[Foo]显式地，是否会为[Foo]上的[AnotherID]列自动创建索引 - 因为表[Goo]上的外键引用列[GID]已经具有主聚簇键索引？
$ b $ pre $ CREATE TABLE [dbo]。[Foo]（
[ID] [bigint] IDENTITY（1,1） NOT NULL，
[AnotherID] [int] NULL，
[InsertTime] [datetime] NULL CONSTRAINT DEFAULT（getdate（）），
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED

[ID] ASC
）WITH [PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON] ON [PRIMARY]
）ON [PRIMARY]

ALTER TABLE [dbo]。[Foo] WITH CHECK ADD CONSTRAINT [FK_Foo] FOREIGN KEY（[Goo]）
REFERENCES [dbo]。[Goo]（[GID]）
ALTER TABLE [dbo]。[Foo] CHECK CONSTRAINT [FK_Foo]

b George

解决方案

SQL Server不会，也不会在外键列上自动创建索引。这是一个广泛传播的城市神话 - 但它只不过是一个神话。

但这是一个公认的最佳做法 - 这是最基本的建议之一进行索引调整 - 在外键列上放置索引。

查看Kimberly Tripp的优秀博客文章获取更多背景信息。

I am using SQL Server 2008 Enterprise. I have a table and one of its column is referring to another column in another table (in the same database) as foreign key, here is the related SQL statement, in more details, column [AnotherID] in table [Foo] refers to another table [Goo]'s column [GID] as foreign key. [GID] is primary key and clustered index on table [Goo].

My question is, in this way, if I do not create index on [AnotherID] column on [Foo] explicitly, will there be an index created automatically for [AnotherID] column on [Foo] -- because its foreign key reference column [GID] on table [Goo] already has primary clustered key index?

CREATE TABLE [dbo].[Foo](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [AnotherID] [int] NULL,
    [InsertTime] [datetime] NULL CONSTRAINT DEFAULT (getdate()),
 CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Foo]  WITH CHECK ADD  CONSTRAINT [FK_Foo] FOREIGN KEY([Goo])
REFERENCES [dbo].[Goo] ([GID])
ALTER TABLE [dbo].[Foo] CHECK CONSTRAINT [FK_Foo]

thanks in advance, George

解决方案

No. SQL Server does not and never has automatically created indices on foreign key columns. It's a wide spread urban myth - but it's nothing more than that - a myth.

But it's an accepted best practice to do so - so that's one of the most basic recommendations for index tuning - put indices on your foreign key columns.

See Kimberly Tripp's excellent When did SQL Server stop putting indexes on foreign key columns? blog post for more background info.

这篇关于外键和索引问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！