Article ID: 291001 - View products that this article applies to.
This article was previously published under Q291001
SQL Server 2000 Service Pack 1 (SP1) adds a new stored procedure, sp_scriptdynamicupdproc, which may improve the performance of Transactional replication when replicating update statements that use custom stored procedures if some of the following conditions are meet:
The sp_scriptdynamicupdproc stored procedure generates the CREATE PROCEDURE statement to create a dynamic update stored procedure. The update statement within the custom stored procedure is built dynamically based on the MCALL syntax and indicates which columns to change. This approach becomes attractive as the number of indexes on the subscribing table grows and the number of columns actually being changed is small. This stored procedure is executed at the Publisher on the publication database.
The syntax that you use is:
The result set is a single nvarchar(4000) column that forms the complete CREATE PROCEDURE statement necessary to create the custom stored procedure on the subscriber.
The default MCALL scripting logic includes all the columns within the update statement that use a bitmap to determine columns that actually changed. If a column did not change, the column is set back to itself. If the column happens to be indexed, extra processing may occur due to index maintenance. If you have several indexes on the subscribing table for which only a few column values are changing, the index maintenance overhead may cause performance problems. The dynamic approach only includes the columns that have changed providing an optimal update string. However, the dynamic update statement may result in extra processing to build the dynamic update statement. Therefore, determining when to use the dynamic approach depends on the users environment. You should prototype the two approaches, and then choose the custom procedure scripting logic that best suits your environment.