Do I need an index on this?

Question

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

Here's a snippet from the db:

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?

Answer 1

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.

Answer 2

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.