In an earlier blog post about database hints, I gave a number of reasons why I dont like to use hints in SQL queries. Today I wanted to follow up to share one situation where I think hints are OK, even necessary.
As described very well in an article in an article by Andrew Kelly in SQL Server Magazine (InstantDoc 129916), hints are sometimes necessary when we wish to minimize logging during bulk loading operations. In particular, when issuing a bulk insert, using the TABLOCK hint is necessary in order to reduce logging to the minimum amount even when recovery mode is set to simple.
I still feel strongly that we should avoid the use of optimizer hints in select statements that are embedded in applications for the reasons that I mention in my original post, but Andrew’s article describes one situation where I think they are acceptable.
If you have comments about hints that you would like to share, please reply.
