Re: Do foreign keys generate implicit indexes?
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 23 Sep 2007 07:25:15 -0500
Check with sysindexes to see if SQL Server does this too.
As Erland mentioned, SQL Server does not automatically index foreign key columns. That task is left to the discretion of the DBA, who might choose not to index the foreign column(s) due to low cardinality and static data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom van Stiphout" <no.spam.tom7744@xxxxxxx> wrote in message news:8qqbf3190hk4cis52502s9th2ebsjjfpd5@xxxxxxxxxx
On Fri, 21 Sep 2007 19:51:14 -0000, "bobdurie@xxxxxxxxx"
<bobdurie@xxxxxxxxx> wrote:
Microsoft Access does this, when you create a relationship between two
tables.
Check with sysindexes to see if SQL Server does this too.
-Tom.
If i create a simple table with a foreign key constraint, does it
create an implicit index on that given ID? I've been told this is
done in some databases, but i need to know for sure if SQL Server does
it. Has anyone heard of this before, on any other databses perhaps?
Heres an example of how the foreign key constraint is being added:
ALTER TABLE [dbo].[administrators] WITH CHECK ADD CONSTRAINT
[FPSLUFSUOXZGAJOJ] FOREIGN KEY([AdministratorRoleID])
REFERENCES [dbo].[administratorroles] ([AdministratorRoleID])
My initial testing seems to indicate adding an index on the foreign
key column helps, but i need to know for sure. Any insight would be
greatly appreciated!
Bob
.
- References:
- Do foreign keys generate implicit indexes?
- From: bobdurie@xxxxxxxxx
- Re: Do foreign keys generate implicit indexes?
- From: Tom van Stiphout
- Do foreign keys generate implicit indexes?
- Prev by Date: Re: Do foreign keys generate implicit indexes?
- Next by Date: SQL Mail setup
- Previous by thread: Re: Do foreign keys generate implicit indexes?
- Next by thread: Re: Do foreign keys generate implicit indexes?
- Index(es):
Relevant Pages
|
Loading
