Post reply

Indexing Strategy with Replication

  • 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

  • June 22, 2005 at 3:09 am

    #567333

    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

  • June 22, 2005 at 7:02 am

    #567404

    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

  • June 22, 2005 at 8:54 am

    #567515

    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

  • June 22, 2005 at 9:01 am

    #567522

    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

  • June 23, 2005 at 5:50 pm

    #568160

    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.

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

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