Friday, March 13, 2009

SQL Server Transact-SQL DML

Performing UPDATES takes extra resources for SQL Server to perform. When performing an UPDATE, try to do as many of the following recommendations as you can in order to reduce the amount of resources required to perform the UPDATE. The more of the following suggestions you can do, the faster the UPDATE will perform.
If you are UPDATing a column of a row that has a unique index, try to update only one row at a time.


Try not to change the value of a column that is also the primary key.

When updating VARCHAR columns try to replace the contents with contents of the same length.

Try to minimize the UPDATing of tables that have UPDATE triggers.

Try to avoid UPDATing columns that will be replicated to other databases.

Try to avoid UPDATing heavily indexed columns.

Try to avoid UPDATing a column that has a reference in the WHERE clause to the column being updated.

Of course, you may have very little choice when UPDATing your data, but at least give the above suggestions a thought.

No comments: