November 19, 2001 at 12:44 pm
I would like to change the pk on several published tables from clustered to nonclustered for better query & input response. These tables participate in bidirectional transactional replication, where each sql server (total of 13) can update rows they own and in turn only replicate changes from that record subset.
I am not sure why the tables were setup with clustered primary keys, except that clustering is the default... anyway, being that the system is working, I am cautious about making such a big change. Replication was origionally setup in 6.5 and then upgraded to 7. Was there something about the index type in ver 6.5? Does anyone know if this will make a difference in ver 7?
November 19, 2001 at 1:28 pm
Don't know if this is a problem as I don't use replication that much. I'll ask around.
Steve Jones
November 21, 2001 at 6:21 pm
I would suggest testing on a copy of the db/table before you spend a lot of time changing your replicated copy. You'll have to drop the subscription and remove the table from the publication before you can make the change. I've got an article posted that discusses how to shortcut the process:
http://www.sqlservercentral.com/columnists/awarren/alterreplicatedtable.asp
Of course if your db isnt that big you can just send over a new snapshot when you're done. Use caution, pretty common to have different users, permissions, triggers, etc, on the subscriber. Might need to capture some or all before you do a snapshot.
Getting the clustered index in the right place is tricky. EM makes the primary key the clustered key by default - better to have it some where than no where and the pkey is often the right place. I totally support testing to see if using it on another column will boost performance.
Andy
November 22, 2001 at 2:24 pm
Yes, testing is a good idea. It sounds like no one is aware of there being an issue with clustered or not being the index type for the table's primary key. Thank you both for your input.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply