locked
Peer to Peer Coupled Sampled - XML Column Problem RRS feed

  • Question

  •  

    This is from the Peer to Peer Coupled Sample (SharingAppDemo-CoupledTracking) included with the SDK.

    Does this need to be a union? It does a distinct select so it will not work to include NText or XML fields.

     

    Code Snippet

    create procedure dbo.sp_order_details_selectchanges (    
      @sync_min_timestamp bigint,  
      @sync_metadata_only int)  
    as
        select  order_id,
                order_details_id,
                product,
                quantity, 
             0 as sync_row_is_tombstone,
             sync_row_timestamp,                        
             sync_update_peer_key,
             sync_update_peer_timestamp,
             sync_create_peer_key,
             sync_create_peer_timestamp
        from order_details
     where sync_row_timestamp > @sync_min_timestamp   
        union
        select order_id,      
       NULL as order_details_id,
       NULL as product,
       NULL as quantity,         
       1 as sync_is_tombstone,
       sync_row_timestamp,
       sync_update_peer_key,
       sync_update_peer_timestamp,
       sync_create_peer_key,
       sync_create_peer_timestamp
        from order_details_tombstone
     where sync_row_timestamp > @sync_min_timestamp 
        order by order_id asc
    go

    • Moved by Max Wang_1983 Friday, April 22, 2011 5:57 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, September 2, 2008 12:25 PM

Answers

  • The first select query is for non-tombstone (or live) rows and the second is for tombstone rows.  The reason for the union is the fact that if you deleted a row with ID=1 then reinserted ID=1, you would get both an insert and a delete where you only want an insert.  If you can guarantee that you are not going to reuse IDs then a 'union all' should work.  If you will reuse IDs, you could alter the query to still use 'union all' to do something like:

    create procedure dbo.sp_order_details_selectchanges (    
      @sync_min_timestamp bigint,  
      @sync_metadata_only int)  
    as
        select  order_id,
                order_details_id,
                product,
                quantity, 
             0 as sync_row_is_tombstone,
             sync_row_timestamp,                        
             sync_update_peer_key,
             sync_update_peer_timestamp,
             sync_create_peer_key,
             sync_create_peer_timestamp
        from order_details
     where sync_row_timestamp > @sync_min_timestamp   
        union all
        select order_id,      
       NULL as order_details_id,
       NULL as product,
       NULL as quantity,         
       1 as sync_is_tombstone,
       sync_row_timestamp,
       sync_update_peer_key,
       sync_update_peer_timestamp,
       sync_create_peer_key,
       sync_create_peer_timestamp
        from order_details_tombstone
     where sync_row_timestamp > @sync_min_timestamp and order_id not in (select order_id from order_details)
        order by order_id asc
    go


    Hope this helps
    Tuesday, September 2, 2008 7:26 PM

All replies

  • The first select query is for non-tombstone (or live) rows and the second is for tombstone rows.  The reason for the union is the fact that if you deleted a row with ID=1 then reinserted ID=1, you would get both an insert and a delete where you only want an insert.  If you can guarantee that you are not going to reuse IDs then a 'union all' should work.  If you will reuse IDs, you could alter the query to still use 'union all' to do something like:

    create procedure dbo.sp_order_details_selectchanges (    
      @sync_min_timestamp bigint,  
      @sync_metadata_only int)  
    as
        select  order_id,
                order_details_id,
                product,
                quantity, 
             0 as sync_row_is_tombstone,
             sync_row_timestamp,                        
             sync_update_peer_key,
             sync_update_peer_timestamp,
             sync_create_peer_key,
             sync_create_peer_timestamp
        from order_details
     where sync_row_timestamp > @sync_min_timestamp   
        union all
        select order_id,      
       NULL as order_details_id,
       NULL as product,
       NULL as quantity,         
       1 as sync_is_tombstone,
       sync_row_timestamp,
       sync_update_peer_key,
       sync_update_peer_timestamp,
       sync_create_peer_key,
       sync_create_peer_timestamp
        from order_details_tombstone
     where sync_row_timestamp > @sync_min_timestamp and order_id not in (select order_id from order_details)
        order by order_id asc
    go


    Hope this helps
    Tuesday, September 2, 2008 7:26 PM
  • Thanks, Phil. That's what I figured, and have been using union all for testing. I appreciate your help.

    Wednesday, September 3, 2008 12:45 PM