none
Table hint for MERGE. RRS feed

  • Question

  • MSDN says,  "The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT statements will be removed in a future version of SQL Server. These hints do not affect the performance of INSERT statements. Avoid using them in new development work, and plan to modify applications that currently use them."  about allowed hints for INSERT statement.

    Should I also avoid these hints for MERGE which inserts records ? 

     

    Thank you.

    Saturday, May 28, 2011 9:22 PM

Answers

  • Overall, you should be restrictive with using table hints, not the least locking hints.

    The reason these hints will be dropped for INSERT is that they don't make sense, as an INSERT statement requires an exclusive lock on the target table.

    For a MERGE statement is different since, there is first a phase where the rows are located, and in this stage I assume that SQL Server uses some type of shared lock. So here it could matter which isolation level you select, but I don't really see why you would bother.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by a1ex07 Saturday, May 28, 2011 10:41 PM
    Saturday, May 28, 2011 10:16 PM

All replies

  • Yes, certainly ,as already mentioned , you must avoid using these hints and start analyzing if you have used somewhere in your queries, to avoid any un-seen consequences interm of application issues.

    Thanks,

     


    Raheel Khan
    Saturday, May 28, 2011 9:56 PM
  • Overall, you should be restrictive with using table hints, not the least locking hints.

    The reason these hints will be dropped for INSERT is that they don't make sense, as an INSERT statement requires an exclusive lock on the target table.

    For a MERGE statement is different since, there is first a phase where the rows are located, and in this stage I assume that SQL Server uses some type of shared lock. So here it could matter which isolation level you select, but I don't really see why you would bother.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by a1ex07 Saturday, May 28, 2011 10:41 PM
    Saturday, May 28, 2011 10:16 PM