question

Obviously, indexing a foreign key allows that there will always be values in the field, even though there are duplicates. Whether you'd want to index it or not would depend on the data-type and whether you were likely to see any performance gains. Considering you would usually join to the table via the foreign key would suggest you would see some gains, but how much depends on table size/usage.

The second point should be moot as you can not have null values in a foriegn key field

The third point. It does help a bit as the Primary key this FK is attached to is likely to have a clustered index. Again, the benefits are dependant on the size of the table and also the size of your hardware (i've had to use query hints to stop using hash joins as I didn't have enough memory on servers before [SQL will not check how much it has free and just tries to use a hash join even if there is none, meaning the query waits until there is enough available, someone at Microsoft needs shooting for thinking that was a good idea!]).

I'm a normal "would be DBA" level developer. I've been handling some databases with a few million records. A lot goes around importing data between database and its clone and then using that clone in the web-app environment.

Well, I've known that keeping PK indexes automatically and so it helps speedup the data access. Now, from this discussion I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient.

For example, I have a table OrgMaster (contains all the Org records) then I have a BookingMaster table (contains all the Booking records). Now, the OrgMaster.Id is being 'referenced' as BookingMaster.OrgId. So, I have an FK for the OrgId-to-Id relationship and I shud 'index' it for better performance of any JOIN operation between both of these tables .. did I get it correctly?

All the above - at the cost of extra overhead of space and time (while inserting record in the table with FK).

I'd request that you provide me a list of points to be considered, like -

• Is FK-index going to eat up too much space\time as table grows few million records?
• In that case, is it worth to go for an FK-index "each time"?
• In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app)
• Any other tricky to speedup JOIN or other such time-consuming lookups?

Thank you.

An FK in your example may possibly speed up the JOIN, but I don't think you'd see a massive effect as you already have an Index on your join and SQl would hash match the results anyway. It would also slow down any insert/updates to BookingMaster. It is worth having a clustered index on these tables as this helps with speed of inserts, but any other indexes cause overheads. It really does depend on how often you are writing to this table and how time critical the loading of each records is.

Is FK-index going to eat up too much space\time as table grows few million records? - Possibly, it depends on your record size and requirements for reporting vs requirements for inserting data, it is always a trade off.

In that case, is it worth to go for an FK-index "each time"? - No, it very much depends on data-types, requirements and simply testing on your side.

In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app) - Anywhere adding the FK affects performance or a lot of inserts are required (the numeric value for a lot depends on the size and capabilities of youre server).

Any other tricky to speedup JOIN or other such time-consuming lookups? - Loads, but one example is any join that uses a function (substring, cast/convert etc..) to split the data as this will not make use of any indexes.

I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient

No specific need for a FK when you use a JOIN, but an Index will usually help.

Whilst a FK will ensure that you have referential integrity - i.e. you cannot accidentlaly create an orphan record - it will delay delete operations whilst the consistency of the database is checked.

Having said that I always have FKs where a relationship exists. Whist the DELETE performance is sometime terrible because of it, I cannot absolutely guarantee that I don't have a bug in my program that might cause data problems.

So when I have a FK in place I can safely use an INNER JOIN knowing that my data will not have any orphans. When I do not have a FK I have to use an OUTER JOIN "just in case" there are orphans.

Any other tricky to speedup JOIN or other such time-consuming lookups

Always always ensure that the datatype of the fields in the JOIN are the same - or use an explicity CAST / CONVERT to force the datatype to be the same.