Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
    
Can you show the relationships between the tables (the SQL)? –  ypercube Oct 6 '12 at 12:40
add comment

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

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

2 Answers

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
add comment

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
1  
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.