Take the 2-minute tour ×

I have a sql server db and have a question regarding foreign key indices.

Here's a snippet from the db:

enter image description here

In programme_affiliates, I have a computed field, this is always set to '2' in this instance.

The reason for it is that it maintains integrity between programmes and programmes_affiliates.

I also have a link to programme_types to ensure that '2' is always valid as a programme type.

I believe that you should always provide an index for a foreign key, but as it is a calculated fixed field, is this necessary in this case?

share|improve this question

migrated from stackoverflow.com Oct 6 '12 at 9:51

This question came from our site for professional and enthusiast programmers.

Can you show the relationships between the tables (the SQL)? –  ypercube Oct 6 '12 at 12:40

2 Answers 2

up vote 1 down vote accepted

Go through this link: http://blog.sqlauthority.com/2010/08/22/sql-server-computed-columns-index-and-performance/

Since all values are constant and same creating non clustered index will not help here.

share|improve this answer

If you need a index or not depends on the query you want to fire on your data. If you have a lot of queries e.g. where you have ProgrammeTypeIdCalculated in your WHERE statements or ORDER BY clauses, then it make sense to set a index on this column.

Edit: Removed my first sentence. FK need to add constraint. I was wrong. Index need to be added (tho my migrations always do it so I checked it wrong. So to the answer: Check the profiler and set indexes as you need depending on your queries.

share|improve this answer
Do you mean FK columns would have index created automatically by SQL Server? If so then I am afraid its wrong. vadivel.blogspot.in/2012/02/… –  vmvadivel Oct 5 '12 at 10:47
Thx for the hint. –  YvesR Oct 5 '12 at 12:03

Your Answer

