am not sure adding indexes on FKs. i read somewhere if your child table contains lot of unique records then there is no point of creating index as index size is same as table size in that case seeks wont be any faster than scans , am i wrong ? thanks for the advise
Yes, you are wrong. Take it to the extreme: you have a billion row child table where every single parentid value is unique. Parentid is not indexed. You need to get the ONE child row for parentid 111222333. You have no choice but to do a table scan to check every single parentid value for equality to 111222333. That cannot be efficient. Index parentid and you will probably get somewhere between 8 and 10 total page reads (btree depth) depending on the width of the child PK and parentid. The table scan without the index could well be many tens or even hundreds of thousands of pages.
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail