SQL Server Bulk Updates and Locking

When doing a large update of a table in a SQL Server database you may gain some performance by locking at the Table level instead of allowing SQL Server to do Page or Row level locking.
This can be accomplished using Table Hints. Table hints override the query optimizer default plan for the executed query. Table hints apply to data manipulation language (DML) statements such as Select, Insert, Update and Delete. Table hints should be specified in the FROM clause. Table hints allow the user to choose a specific locking method, indexes to use, or other options for DML statements.
It should be noted however that you are second guessing the query optimizer and if conditions change in the database that might make the query work better SQL Server may still use your less efficient table hint.
The ProDataMan way
If you use Optimizer Hints AKA Table Hints make sure to document any scripts or procedures that use table hints so they can be reviewed when changes to the database schema are made that might affect the Table Hint.

Leave a Reply

Your email address will not be published. Required fields are marked *