I have found a solution though adding:
@identityrangemanagementoption = N'manual'
when I re-add the article using sp_addarticle, means that the automatically created sps used to replicate the data don't try and update the identity column and thus are created succesfully on the subscriber rather than raising the error.
A primary key on a INT IDENTITY ought to be very close to optimal, and as such should not lead to a significant index fragmentation.
However: since your PK is (by default and unless you specifically changed it) also your clustering index, and the leaf level of the clustering index are the actual data pages.
If your data structures has changed significantly over time, new columns were added, others possibly dropped, the length of string-based columns changed - this can lead to significant index fragmentation (on the leaf level) since pages need to be rearranged to make room for new data columns.
Also: if you have a good number of variable length columns (varchar(x)) and those have been updated, if the length of the varchar column increases, this can lead to page splits. This is especially true if you have a FILLFACTOR of 100% on your PK index - in that case, even a single extra character can lead to a page split - one page is split into two, the data distributed amongst the two new pages and this contributes to index fragmentation.
So in light of all this, consider:
- even having a maintenance plan for a perfect
INT IDENTITY index isn't a bad idea
- if you have lots of
varchar columns, and they could well be growing over time (text gets longer and longer) - consider a FILLFACTOR lower than 100% (the default)
PS: if you still have your situation before the reorganization - try this query (and put your table name as the second parameter for dm_db_index_physical_stats) - this will show you index fragmentation on the PK index, on every level of the index:
SELECT
'Table Name' = t.NAME,
'Index name' = i.NAME ,
ips.alloc_unit_type_desc,
ips.index_depth,
ips.index_level,
ips.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PutYourTableNameHere'), NULL, NULL, 'DETAILED') ips
INNER JOIN
sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN
sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
i.index_id = 1
Best Answer
As there is a primary key defined on identity column you wont be able to directly alter this column.
Both the approaches that you have mentioned in your question can be used and downtime depends on how your server is performing and number of rows reside in that table.
First drop the PK
Alter Column
Add Primary key
This approach usually does not take much time. In my environment it takes mare seconds on big tables which have more than 5 million rows.
You can use this approach as well. However, for this approach you need more downtime than Approach one as you have to sync the tables.