I want to know the differences between the join hints (MERGE, HASH, LOOP, REMOTE). So, in which situations I should which one and why? How to know which hint I should use?
Are there any pro's or contra's of using the hints?
If I use the correct hint, will the performance of my query will be better? Are there also bad things?
And are there any kinds of limitations when I'm using these hints?
The most widely use hint is nolock which many power users frown upon. SQL Server discourages the use of hints. The SQL Server engine is very, very powerful and sophisticated and will generally produce the 'best' query plan by itself and will often ignore most hints. If an index is not being used its either not selective enough (so it doesn't pay for SQL Server to use) or its statistics are stale. Very, very rarely in SQL Server 2008 and on do you need hints - you may need more/better indexes or better SQL.
The only time you may need hints is if you try fixing your indexes and SQL Server is not using them or its not joining things using the right type of Join as indicated by the Actual Query Plan. But such situations are very rare.
