Indexing Strategy with Replication

Question

Indexing Strategy with Replication

Greg Grow

SSCrazy

Points: 2386
More actions
June 21, 2005 at 12:48 pm

#88745

I am new with a new company and they are going to be using Replication. It is not running yet, but will soon be up. My question is related to clustered indexes with Replication. The database currently has all clustered indexes on there GUID Primary Keys. I am not fond of GUIDs, but especially not fond of Clustered GUIDs. I was told by someone that Replication wont work if the Clustered Index is on a non-unique column. If this is true, that would be a huge drawback with range searches. Can someone please explain some of this to me. I am looking for how SQL Server Replication manages or uses indexes? Why would a table need a Clustered Key on the primary key or some other table that is unique?

Thanks
Greg

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Ibison SSCertifiable Points: 5303 More actions · Answer 1

Merge replication relies on uniqueness of GUIDS so there is a unique index placed on the GUID column (having the rowguid attribute) but this is not the same as a clustered index. There can be only one clustered index per table, and by default this will be on the PK. So, in all likelihood the clustered index will have been already assigned prior to replication and whether this is the case or not, the replication setup will not try to change the publisher's schema and implement the clustered index.

HTH,

Paul Ibison, http://WWW.ReplicationAnswers.Com

Paul Ibison
Paul.Ibison@replicationanswers.com

Ian Yates SSCoach Points: 19738 More actions · Answer 2

I guess that it is something that Joe Celko might say, but an index is a performance boosting concept and might influence how SQL Server executes your query, but should never affect the results of the query... Same goes for your index & replication - it may well run faster with a different index, but the end result should always be the same. (Well most of the time indexed views, etc muddy the waters!&nbsp

Greg Grow SSCrazy Points: 2386 More actions · Answer 3

For Transactional Replication, does a Clustered Index need to be on a Unique Column or can I choose any column for the Clustered Index? I am trying to find anything that I can take back to the lead architect so that he can feel a little more comfortable with moving the clustered index off of the GUIDs. I can't imagine why anyone with there right mind would want to sort the data of table by clustering the GUIDs. It is random, and therefore it would seem the performance would, "SUCK." This clustered index not only effects the order of the table, but also the key in the non-clustered indexes.

Thanks

Greg

Greg Grow SSCrazy Points: 2386 More actions · Answer 4

One more question, are there different indexing strategies with Replication? I have done replication long ago, and I don't remember needing to care about the indexing strategies.

Greg

Ian Yates SSCoach Points: 19738 More actions · Answer 5

That's because you don't need to care so much - the replication will work regardless of if or where you have a clustered index, or any other indices for that matter... They will improve the performance quite significantly but the DML queries & statements that the replication agents run to transfer data or merge data, etc should be exactly the same.