Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 SQL Replication (Snapshot)

Author  Topic 

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2012-10-11 : 10:06:47
is there any advantage of using SQL Snapshot Replication over a approach where I accomplish the same result with just a few SQL statements.. such as

DROP the destination table
SELECT INTO destination table FROM source table



Nick W Saban

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2012-10-12 : 05:56:27
Here is the answer:

Replication will only affect records added, updated or deleted since the last replication whereas Drop/Insert will of course affect all records. For smaller result sets this may not be an issue however if you’re looking at hundreds of thousands/millions or records, there could be a substantial impact on the database performance (especially if there are indexes involved). Also, Drop/Insert will result in larger transaction log activity than replication.

Nick W Saban
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-10-20 : 19:23:21
No. The snapshot agent copies ALL of the records, not just changes.

I still often choose snapshot replication for this task, particularly when there are many tables I need to copy on schedule.
Go to Top of Page
   

- Advertisement -