When does SQL Server decide to use UPDATE as against DELETE/INSERT pairs

When does SQL Server decide to use UPDATE as against DELETE/INSERT pairs

Post by Jagannathan Santhana » Sat, 06 Apr 2002 01:16:09



Hello
   We are using 6.5 SQL Server Replication from 65 remote servers to a
Central Server. The remote Servers are setup to call custom Remote
Stored Procedures for INSERT, DELETE and UPDATE operations on tanles.

Using a cursor we have a job that updates a specific column in the above
set of table(s). This is accomplished by using a WHERE clause involving
the PK value alone.

EX:
     UPDATE <table_name>
     SET
            <table_name>.<column_name> = <value1>
     WHERE
            <table_name>.PK_COL = <value2>

However, what we have noticed is that it causes the following calls to
fire on the remote Subsription Server:
         call <delete procedure>
         call <insert procedure>

Can anyone tell us why this is happening ? I can justify the above if we
were using the UPDATE command without a WHERE clause on the Publishing
Server.

Thanks in Advance

Jagannathan Santhanam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

When does SQL Server decide to use UPDATE as against DELETE/INSERT pairs

Post by Hilary Cotte » Sat, 06 Apr 2002 02:11:11


most of the time an update under the covers is done as an
insert delete pair.

Have a look at Q135871 for more information on the cases
where an update in place is done.

Quote:>-----Original Message-----
>Hello
>   We are using 6.5 SQL Server Replication from 65 remote
servers to a
>Central Server. The remote Servers are setup to call
custom Remote
>Stored Procedures for INSERT, DELETE and UPDATE

operations on tanles.
Quote:

>Using a cursor we have a job that updates a specific
column in the above
>set of table(s). This is accomplished by using a WHERE
clause involving
>the PK value alone.

>EX:
>     UPDATE <table_name>
>     SET
>            <table_name>.<column_name> = <value1>
>     WHERE
>            <table_name>.PK_COL = <value2>

>However, what we have noticed is that it causes the
following calls to
>fire on the remote Subsription Server:
>         call <delete procedure>
>         call <insert procedure>

>Can anyone tell us why this is happening ? I can justify
the above if we
>were using the UPDATE command without a WHERE clause on
the Publishing
>Server.

>Thanks in Advance

>Jagannathan Santhanam

>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.


 
 
 

When does SQL Server decide to use UPDATE as against DELETE/INSERT pairs

Post by Jagannathan Santhana » Sat, 06 Apr 2002 02:30:33


Hilary
 What is Q135871 ? Is it a search pattern I can use in this user-group ?

Thanks
Jagannathan Santhanam

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

When does SQL Server decide to use UPDATE as against DELETE/INSERT pairs

Post by Hilary Cotte » Sat, 06 Apr 2002 12:15:53


try this link
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q135871


Quote:> Hilary
>  What is Q135871 ? Is it a search pattern I can use in this user-group ?

> Thanks
> Jagannathan Santhanam

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!