none
LEFT JOIN CHANGETABLE intention of SelectIncrementalInsertsCommand RRS feed

  • Question

  • Hi All,

    I just enabled Change Tracking on my existing SQL 2008 database.  It is a running database containing massive records. I generated the sync stuff to support my synchronization process. 

    On the generated command, I found the JOIN clause within the IF @sync_initialized = 0 block. As per my understanding, the block is intended to download record if no sync initialized. In other words, the block will initialize client database.

    Here is the snippet I mean.

    	this.SelectIncrementalInsertsCommand.CommandText = @"
    
    	IF @sync_initialized = 0 
    
    		SELECT dbo.SaleType.[SaleTypeID]
    
    			, [Name]
    
    			, [Description]
    
    		FROM dbo.SaleType 
    
    			LEFT OUTER JOIN CHANGETABLE(CHANGES dbo.SaleType, @sync_last_received_anchor) CT 
    
    				ON CT.[SaleTypeID] = dbo.SaleType.[SaleTypeID] 
    
    		WHERE (CT.SYS_CHANGE_CONTEXT IS NULL OR CT.SYS_CHANGE_CONTEXT <> @sync_client_id_binary) 
    
    	ELSE 
    
    	BEGIN
    
    		-- ommitted
    
    	END ";
    
    
    
    

    I thought, all records should be downloaded in that circumstance to have them stored locally. Hence, the LEFT OUTHER JOIN to CHANGETABLE is unnecessary there. I removed the join to have all the records downloaded during initial sync.

    I'm wondering the intention of the JOIN clause there (as it can be removed to get the same result). Could somebody explain?
    Appreciate for any help.

    regards,

     

    Agung

     

    Wednesday, September 22, 2010 1:43 AM

All replies

  • if you're doing bidirectional syncs, the client does uploads first before downloads. where clause against the changetable makes sure the client doesnt download the same rows it just uploaded.
    Wednesday, September 22, 2010 3:24 AM
    Moderator
  • Hi JuneT,

    Thanks for your respond. I still confused about this, however.

    I did a BiDirectional Sync on SQL 2008 R2 (server) with SQL CE (client). As far as I know, the sync will execute InsertCommand, DeleteCommand, and UpdateCommand to upload. While to download, it will execute SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand and SelectIncrementalDeletesCommand.

    In my scenario, I need to turn Change Tracking on into my existing database and start to do sync. Of couse, the existing rows will not have any tracking information.

    Initially there is no row on my SQL CE (sdf). On the initial sync, the process will be upload. There is no row to upload as the sdf is empty. Then the sync will do download.

    As it is the first sync, my SelectIncrementalInsertsCommand will be executed. I believe the @sync_initialized = 0 at this stage. In my view, there should be no worries the client download the same rows it just uploaded as there was no upload. Hence, the LEFT JOIN to CHANGETABLE is not required on this block.

    SelectIncrementalUpdatesCommand and SelectIncrementalDeletesCommand will not retrieve any records at this stage as there is no modification/deletion on the main database.

    On the next sync, (it means @sync_initialized is not 0)  JOIN to CHANGETABLE is required to avoid the client download the same rows it just uploaded. However, the @sync_initialized = 0 block will not be executed anymore. It will execute the ELSE block instead. Hence, the JOIN to CHANGETABLE is mandatory on the ELSE block only.

    - is my understanding about the @sync_initialized = 0 block and JOIN to CHANGETABLE above correct?

    - when does the sync set @sync_initialized?

    Could you please elaborate?

     

    Thanks,

    Agung

     



     

    Monday, October 11, 2010 7:29 AM