June 4, 2019 at 9:15 pm
Hi everyone,
We have a table(Table1)in sql server 2012, this table has below columns.
TransactionID(bigint)
TransactionDate datetime( it's the insertion date of record)
and some other columns
This table will be replicated(in transactional replication) to one database from three different publications(sql server 2012). The transactionID is an identity column with different range of ID's in each publication and act as primary key clustered index in each publication.
In subscriber, I want to use TransactionDate as Clustered index(non-unique), in order to avoid page split and use TransactionID as Non-clustered index primary key? Is this right approach? or should I use both transactionID and transactionDate as clustered index(in order to avoid the overhead of uniqifier)?
I would appreciate if someone help me on this regard.
Thanks
Aspet
June 5, 2019 at 1:26 pm
you could keep your existing clustered index on the publisher.
then set the article properties to not copy indexes. then create a post snapshot script that creates the clustered index to your requirements. This means the indexes are different on subscriber. If you reinitialize the subscription your "post" script will apply every time
I'm not sure why moving from a bigint to datetime (if I remember, stored as 2 int values- if i'm wrong talk to itzig ben-gan) will avoid page split, you really want to look at getting your index right for the queries you run rather than focus on page split.
a caveat, on big tables with clustered indexes on datetimes you can have issues deploying snapshots.
we had a clustered index on "datetimemodified".. as the snapshot was generated, if a record was changed while creating the snapshot then it appeared in the BCP file twice. Which meant we got PK violations when applying it
MVDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply