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.

CLUSTERED INDEX or NONCLUSTERED

I have 3 table A, B, C

Table A (15 field, 4 fields indexed and Primary Key) approximate rows: 50.000 60.000

Table B (18 field, 6 fields indexed and Primary Key) approximate rows: 350.000 500.000

Table C (16 filed, 9 fields indexed and Primary Key) approximate rows: 500.000 1.000.000

Structure is something like this:
A (master) --> B (detail) --> C (sub detail)

On each 3 table is added new record, in table C the record is added after a search in table B.
My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX

Thanks
Sorry for my englishIt is not clear about relations between tables (number of fields, etc.) by anyway clustered index for PK and nonclustered for others will be OK.|||Not enough info, these links may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TuningofaDifferentSort.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp|||Thank you for your answer.
The diagram is attached, form left to right table A; B; C|||The diagram|||Still not enough info. Some questions:

What is your ratio of inserts to queries? Are you heavy insert or heavy queries or both?

What is typically used for your select criterias?

I would reccomend you start with reading those articles and you may play around with "set statistics IO on" to evaluate your logical IO when you have added a clustered index, taken it off, added a nonclustered index, etc. This to me is the best advice to become self sufficient on indexing questions.

HTH

Clustered Index on Indexed View from ADO.NET

I have a clustered index on an indexed view in sql server 2000. When I do a simple select in query anaylser from this view I can see from the execution plan in profiler that the clustered index was used to return the data, hence improving performance of the underlying select(this is why I am using the indexed view). However, if I run the query from an asp.net page using the sql provider I can see the call in profiler but the clustered index is not used, hence reducing the performance of the call considerably.
If anyone has experienced this please let me know.
CheersViews are query rewrites just persisted so the query optimizer usually ignore the execution plan. Try the link below to see if you can make changes to your create a View statement to improve performance. Hope this helps.
http://www.sqlteam.com/item.asp?ItemID=1015

Clustered index

I have a few tables with a clustered index (SQL Server 2000).
When I open some of these tables, enter a new value into an indexed field
and reopen the table (actually click Run in a context menu), the table is
ordered by the indexed field.
In some other tables, the new value is not ordered and remain in the last
row.
Why is this difference between the tables and what is a proper behaviour of
the clustered index?
Thanks."Vik" <viktorum@.==yahoo.com==> wrote in message
news:%23F%23A0ToEIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have a few tables with a clustered index (SQL Server 2000).
> When I open some of these tables, enter a new value into an indexed field
> and reopen the table (actually click Run in a context menu), the table is
> ordered by the indexed field.
> In some other tables, the new value is not ordered and remain in the last
> row.
> Why is this difference between the tables and what is a proper behaviour
> of the clustered index?
> Thanks.
>
This is really a feature of whatever application you are using to query the
table. When you "open" a table in Enterprise Manager for example it will
just query the table by selecting all columns and rows but it won't specify
an ORDER BY clause. That means the ordering is uspecified and will be
determined by whatever execution plan the server chooses at runtime. If
ORDER BY isn't specified but the table has a clustered index then there is a
fair chance that the rows will be returned in an order that matches that
index - although that's definitely not guaranteed.
If you want to be sure what your application is doing, then use SQL Profiler
to capture the statements that it uses and see if it includes an ORDER BY
clause.
Just remember that a table is an unordered set of rows. Unless you query it
using ORDER BY you should assume nothing about the order of rows returned.
--
David Portas|||While this is 100% true, it doesn't tell the whole story.
I have noticed the default order of rows being "sorted" on a non-ordered
select for years. At a casual view, this seems to actually be a sort, even
though it isn't. In particular, I've noticed this on freshly built tables
with (and without) clustered indexes and where the internal blocks used to
store the data were contigious. When clustered indexes are used, I've seen
the "sort" maintain itself after an insert.
At one point I was convinced this was reliable behavior, sadly, it isn't.
Sure looks that way though. I suspect it has to do with the physical order
the data was entered into the database and the way clustered indexes are
implimented.
So, it is not automatically a feature of something, it could be the order
the data is coming out of the database because of the order it was put in
and/or where the insert occured (because of the clustered index).
Jay
> This is really a feature of whatever application you are using to query
> the table. When you "open" a table in Enterprise Manager for example it
> will just query the table by selecting all columns and rows but it won't
> specify an ORDER BY clause. That means the ordering is uspecified and will
> be determined by whatever execution plan the server chooses at runtime. If
> ORDER BY isn't specified but the table has a clustered index then there is
> a fair chance that the rows will be returned in an order that matches that
> index - although that's definitely not guaranteed.
> If you want to be sure what your application is doing, then use SQL
> Profiler to capture the statements that it uses and see if it includes an
> ORDER BY clause.
> Just remember that a table is an unordered set of rows. Unless you query
> it using ORDER BY you should assume nothing about the order of rows
> returned.
> --
> David Portas
>