Is a FK also an index

Question

Is a FK also an index

digitalox

SSCommitted

Points: 1830
More actions
March 15, 2010 at 11:40 am

#386503

Howdy,
For some reason my understanding was that creating an FK constraint also created an index, but after looking at the table I'm not sure.
Can anyone clarify this or point me in the right direction - do I need to create a NC index and FK constraint, or just FK?
Thanks,
Scott

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

No, creating a FK does not create an index on the same column(s). If you want an index on your FK column(s), you must explicitly create it.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

digitalox SSCommitted Points: 1830 More actions · Answer 2

Good to know, thanks for the quick reply.

Regards,

Scott

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

This is actually a common misconception. As John said, you will need to create an index manually for each FK.

Also of worth, I covered this topic with a script in a recent blog article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

daustinash SSC Eights! Points: 859 More actions · Answer 4

Probably a misconception due to the fact that a Primary Key DOES add a unique index by nature.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

David Nash-367764 (3/15/2010)
Probably a misconception due to the fact that a Primary Key DOES add a unique index by nature.

That probably is true.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

digitalox SSCommitted Points: 1830 More actions · Answer 6

Thanks - yes i found a script (could be yours) that identifies the FKs without indexes. I just used it not to long ago. I think I just had a case of the Mondays...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 8

Well - it depends. If you use the diagramming tool to set up your FKs it will automatically generate indexes.

Except when it breaks when you're saving. Then you can find it actually removes existing ones