Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Change Not For Replication Value for SQL Server Identity Columns

By:   |   Read Comments (13)   |   Related Tips: More > Replication

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.

When setting up replication there are many things to think about and many options you can choose from when setting up your publications and subscriptions.  In most cases replication is an after thought and not part of the original database or application design, so there may be some required changes that need to be done when replication is setup.  The most important part is that the table has a primary key.  All tables should have a primary key when they are created, but sometimes this is not addressed and for replication to work this needs to be setup.  In addition, if you are using merge replication you need to have a RowGuid column.  Also, if you use identity columns you need to make sure the not for replication parameter is turned on. 

Luckily when setting up replication SQL Server will handle the RowGuid and the not for replication settings for your identity columns. but the primary key issue is something you still need to address manually.  Although it is great that SQL Server handles the not for replication setting for you, what is the process to turn this off or turn this on for tables? 

The "Not For Replication" setting for identity columns allows replication to pass the ID value from the publisher to the subscriber without the subscriber creating a new ID.  Since identity columns will always generate a new number when records are inserted, this setting allows you to ensure the values on both your publisher and subscribers stay in synch.

For SQL Server 2005 this option can be set when designing or creating a new table as shown below in the highlighted section.

For SQL Server 2000 this option can be set when designing or creating a new table as shown below in the highlighted section.

To create a table with the not for replication syntax using a script you would do something as follows:

CREATE TABLE [dbo].[Table_1](
[ProductName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductDescription] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

As mentioned above replication is not always thought about when tables are first created and therefore this "Not For Replication" option is not usually set to YES.  Also, I mentioned that when replication is setup these options are changed for you by the replication setup process.  But if you remove replication this setting stays as yes or if you need to keep a development schema (which is not replicated) in synch with your production schema (which is replicated) how can you do this without having to manually change each table via the table designer?

SQL Server 2005
In SQL Server 2005 a new system stored procedure has been created to allow you to turn on or turn off this "Not For Replication" setting.  This new stored procedure is sys.sp_identitycolumnforreplication.  Following is the code of this new SP.  As you can see it makes a call to a some process %%ColumnEx which is a bit cryptic.


-- Name:
--  sp_identitycolumnforreplication
-- Description:
--   This procedure allows customers to set the NFR on
-- identity columns for a particular table.
-- Returns:
--   0-Success 1-Failure
-- Security: DBO check
-- Requires Certificate signature for catalog access
CREATE PROCEDURE [sys].[sp_identitycolumnforreplication]
@object_id  INT,
@value      bit
@identity_column sysname

AND IS_MEMBER('db_owner'0
(2105014, -1)

@identity_column NULL

SELECT @identity_column name 
FROM sys.columns
WHERE OBJECT_ID @object_id
IF @identity_column IS NOT NULL
%%ColumnEx(ObjectID @object_idName @identity_column).SetIdentityNotForRepl(Value @value)
IF @@ERROR <> 


By using this new SP along with the sp_msforeachtable which iterates through all tables you can turn this on for all tables or off for all tables as follows:

This script turns it YES for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 1'

This script turns it NO for all tables that have an identity column.

EXEC sp_msforeachtable @command1 = '
declare @int int
set @int =object_id("?")
EXEC sys.sp_identitycolumnforreplication @int, 0'


SQL Server 2000
In SQL Server 2000 it is not quite as simple to make this change.  There is not a stored procedure like there is for SQL Server 2005.  The following code snippet will allow you to change this value across the board for all tables in a database as well, but this is updating the system table syscolumns directly.  Most of what you read will tell you not to update the system tables directly.

This script turns it YES for all tables that have an identity column.

xupdate syscolumns
set colstat = colstat | 0x0008
where colstat & 0x0008 = 0 -- ie not already "not for replication"
and colstat & 0x0001 <> 0 -- ie identity column

When trying to run this you may get the following error message.  You need to make this change in order to run these queries.

Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

Next Steps

  • If the need arises to turn on or turn off the not for replication setting now you now how to go about making the change
  • Keep in mind that when changing this value using the table designer, SQL Server creates a temporary table, drops the existing table and renames the new table.  This is not a simple update.
  • Be careful changing system tables.  A wrong update could be disastrous.  Make sure you have database backups prior to changing any system tables directly.

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Monday, April 11, 2016 - 9:46:20 AM - mbourgon Back To Top

 Greg, thanks, this saved our butt yesterday.  We had a table that was using triggers to update a secondary table, and because of the setup the NOT FOR REPLICATION was breaking things.  Being able to turn the thing off was a lifesaver.

That being said, sometime since yesterday it got reenabled.  And no idea why.  *sigh*

Wednesday, September 03, 2014 - 7:21:15 PM - Ilia Back To Top



thank you for your articles it was very helpfull;


I'm running to into problem with idenity column;

I have in both subscriber and publisher NOT FOR REPLICATION set to Y

when I'm doing insert into publisher table Replication Monitoe showing the following error

Cannot insert explicit value for identity column in table 'AdvertisementPartner' when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)
Get help: http://help/544


I've run
SET IDENTITY_INSERT csTrafficSchool2.dbo.AdvertisementPartner ON;

manualy insert record into subscriber DB -- everything was working

manualy insert new record into publisher DB got the same error

could you guide me where to lo at



Friday, June 13, 2014 - 1:54:25 AM - Bhavdeep Back To Top



This is Bhavdeep. I am able to do table level replication from the database. If I want to replicate few column of the table still I'm able to do that. But, here If I want to replcate only few rows from the table than how to proceed...???

Please tell me the exact procedure / steps to do the ROW LEVEL REPLICATION.



Thursday, May 08, 2014 - 1:56:33 PM - SureShot DBA Back To Top

You're a life saver!  I enabled a few DBs for replication in our Prod environment and it broke our build/deploy process.  I removed the replication but it had left all sorts of remnants.  The last remaining was the "NOT FOR REPLICATION" for every table in every DB.  Just when I was panicking on how to get rid of 'em, I came across your article.  Everything is fixed now.  Thank you very much!


Wednesday, June 26, 2013 - 5:35:19 AM - Nhat Nguyen Back To Top

Can you help me code SQL , I have 2 table with data the seem, but when I copy data from FolioTransaction to BKFolioTransaction the system error:

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'SMILE_FO.dbo.BKFolioTransaction' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I know 2 table have [TransactionID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, I can't not copy :(

Can you help me???

Your code:

SET IDENTITY_INSERT dbo.BKFolioTransaction ON;

INSERT INTO SMILE_FO.dbo.BKFolioTransaction   
 select * from SMILE_FO.dbo.FolioTransaction -- where RefNumber [email protected]
SET IDENTITY_INSERT dbo.BKFolioTransaction off;

Thank alot



Wednesday, April 03, 2013 - 12:37:17 PM - Vlad Back To Top

Great article, solve my problem.

Wednesday, November 14, 2012 - 9:58:14 AM - Rantu Back To Top

I can't create this procedure for MS SQL 2005


Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '%'.

Msg 102, Level 15, State 1, Procedure sp_identitycolumnforreplication, Line 41

Incorrect syntax near '%'.




Wednesday, August 22, 2012 - 4:47:27 PM - Greg Robidoux Back To Top

Sure you could drop the triggers on the subscriber if you do not want them to fire again.

You can also use the NOT FOR REPLICATION option for triggers.  Take a look at this: http://msdn.microsoft.com/en-us/library/ms152529(v=sql.105).aspx

Wednesday, August 22, 2012 - 2:57:38 PM - Ryan Back To Top

Hello Greg,

Thanks for your reply. Well it seems the problem was created by some triggers that were updating the table at subscriber. So I disabled the triggers, so its working now. Is it safe to disable the triggers at subscriber, if only the replication agent will update the subscriber database? Its a one way pull transactional replication.


Wednesday, August 22, 2012 - 9:14:24 AM - Greg Robidoux Back To Top

Hi Ryan, if the columns are already set for "not for replication" this should not be an issue if you try to setup replication again for these tables. You don't need to set it to "no" first.

Tuesday, August 21, 2012 - 3:18:38 PM - Ryan Back To Top


If a database has been replicated before and then the replication has been removed, the identity columns will still have "Not For Replication" option set to Yes. Then if the database is replicated again, will there be any problem at subscriber during inserting in the identity columns? I am facing this problem with a database with "Explicit value must be specified for identity column in table tb1 either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column." even though the table at subscriber has the option set to Yes. So in this case (when a database has been replicated before), do I need to chnage this option to No for all identity columns before replicating the database again?



Tuesday, November 02, 2010 - 9:26:31 AM - Greg Robidoux Back To Top

For SQL 2005 and SQL 2008 replication will add the "NOT FOR REPLICATION" setting.  I am not sure about SQL 2000. Thanks for pointing this out in the article.

Tuesday, November 02, 2010 - 8:50:42 AM - Alias John Brown Back To Top

Hello Greg,


You said that the replication process will change the "Not for replication" property of an identity column for you. Are you sure? When I tried to set up replication on SQL Server 2000, it omitted the tables with identity columns with a warning.

Learn more about SQL Server tools