Clustered Indexed View or Custom Sync Object?

sql2k sp3
If you had more columns on Subscriber than Publisher,
would you be more likely to use Clustered Indexed Views or
Custom Sync Objects to implement 1 way, Transactional
Replication? Pros and Cons for each?
TIA, ChrisR
ChrisR,
I personally would prefer to use indexed views.
I suppose simplistically the downside is that there is increased overhead on
the publisher to maintain the materialized data. The upside is that there is
increased transparency so far as maintenance of replication is concerned.
Regards,
Paul Ibison
|||the problem is that for Chris Indexed Views won't support sp_repladdcolumn
and sp_repldropcolumn.
I am mystified as to why what works for me on my machine doesn't work on
ChrisRs. I am revisiting this script and I am hoping ChrisR and others can
test it. There may be a bug somewhere here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ChrisR,
> I personally would prefer to use indexed views.
> I suppose simplistically the downside is that there is increased overhead
on
> the publisher to maintain the materialized data. The upside is that there
is
> increased transparency so far as maintenance of replication is concerned.
> Regards,
> Paul Ibison
>
|||I thought it couldnt be done. Thats what I got from the
replies to my post:
sp_repladdcolumn on Indexed View
08/04/04 4:24 pm.
Did I misunderstand?

>--Original Message--
>the problem is that for Chris Indexed Views won't support
sp_repladdcolumn
>and sp_repldropcolumn.
>I am mystified as to why what works for me on my machine
doesn't work on
>ChrisRs. I am revisiting this script and I am hoping
ChrisR and others can[vbcol=seagreen]
>test it. There may be a bug somewhere here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
>news:OVm9MNphEHA.1356@.TK2MSFTNGP09.phx.gbl...
increased overhead[vbcol=seagreen]
>on
upside is that there[vbcol=seagreen]
>is
replication is concerned.
>
>.
>
|||Sorry for the confusion.
You can't use sp_repladdcolumn or sp_repldropcolumn on an indexed view which
you are publishing for replication.
The Custom Sync object works for me.
Here is the script that works for me.
Here is my TransDTL.SQL script.
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10),
[TranCode] [char] (4),
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10),
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23),
[MerchName] [varchar] (25),
[City] [varchar] (15),
[State] [varchar] (3),
[RejectReason] [varchar] (15),
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10),
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4)
) ON [PRIMARY]
GO
--this is my replication script
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
go
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--creating subscription
sp_addsubscription 'ChrisR','all', @.@.ServerName, 'ChrisRSub'
GO
--startup snapshot agent
DECLARE @.string varchar(200)
SET @.string = 'exec msdb.dbo.sp_start_job @.job_name =
'SELECT @.string = @.string + char(39) +
msdb.dbo.sysjobs.name + char(39) FROM
msdb.dbo.sysjobs,ChrisR.dbo.syspublications
WHERE ChrisR.dbo.syspublications.name = 'ChrisR'
AND ChrisR.dbo.syspublications.snapshot_jobid =
msdb.dbo.sysjobs.job_id
exec (@.string)
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=1
GO
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=5
go
select * from transdtl where transdtlkey=5
GO
use chrisRsub
GO
select * from transdtl where transdtlkey=5
GO
use chrisR
GO
insert into
transdtl(TransDtlKey,CustomerKey,SerialNbr,TranCod e,TransDate,TransDateShort
,TransDateMonth,TransDateYear,TransAmt,RefNbr,Merc hName,City,State,RejectRea
son,PostDate,PostDateShort,PostDateMonth,PostDateY ear,CreateDate,MerchSIC)
select
TransDtlKey+100,CustomerKey,SerialNbr,TranCode,Tra nsDate,TransDateShort,Tran
sDateMonth,TransDateYear,TransAmt,RefNbr,MerchName ,City,State,RejectReason,P
ostDate,PostDateShort,PostDateMonth,PostDateYear,C reateDate,MerchSIC from
transdtl where transdtlkey=5
go
select * from transdtl where transdtlkey=105
use chrisRSUB
GO
select * from transdtl where transdtlkey=105
go
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:9c2001c486c6$432bff60$a501280a@.phx.gbl...[vbcol=seagreen]
> I thought it couldnt be done. Thats what I got from the
> replies to my post:
> sp_repladdcolumn on Indexed View
> 08/04/04 4:24 pm.
> Did I misunderstand?
>
>
> sp_repladdcolumn
> doesn't work on
> ChrisR and others can
> increased overhead
> upside is that there
> replication is concerned.