Home > IT Answers > SQL Server > Set identity to not for replication in T-SQL
Db23
290 pts.
 Set identity to not for replication in T-SQL
ALTER, SQL Server 2000, T-SQL
Hi, Does anyone know the TSQL to change the identity column on a table in SQL Server 2000. And also the TSQL to change trigger to not for replication. Thanks

Software/Hardware used:
ASKED: October 20, 2009  5:58 AM
UPDATED: October 23, 2009  6:49 PM
RELATED QUESTIONS
Answer Wiki:
To change the column... <pre>ALTER TABLE YourTable ALTER COLUMN YourColumn DROP NOT FOR REPLICATION</pre> To change the trigger just do a ALTER TRIGGER and add the NOT FOR REPLICATION to the trigger definition.
Last Wiki Answer Submitted:  October 22, 2009  7:38 pm  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

What do you mean by change the identity column?

Denny Cherry
 64,520 pts.
 

Columns which are set as identity field.
create table(
int id idenitity(1,1)
)

Db23
 290 pts.
 

Thank you for the reply

The changing column identity field syntax works on SQL Server 2005 but when I run the same command in SQL Server 2000, it is returning Incorrect syntax near ‘NOT’.

I have read that you can change the identity column in 2000 by alter the system tables, but I don’t want to do this, as one of our DBA indicated that it not a good idea to change the system table and that is why they remove it in 2005.

Is there any other way beside alter the system tables and how safe is it to alter the system tables? Another option is to script out the database, make the changes to the script, recreate the database and import all the data to the created database.

Do you know why the NOT FOR REPLICATION isn’t added to SQL Server 2000 by default in the first place? Does adding these option to database which will not be configure for replication affect their performance?

Thanks

Db23
 290 pts.
 

Changing system tables is never recommended as if can have disastrous repercussions if not done correctly.

In SQL 2000 you’ll probably want to recreate the table under a different name with the different options set, then move the data into the new table, then rename the tables, rebuild the indexes and add any foreign constraints.

There are hundreds of features which aren’t included in SQL 2000 which have been included in SQL 2005 and SQL 2008. As to why that one feature wasn’t included in SQL 2000 I’ve got no idea. SQL 2000 was only the second version of SQL Server which included replication, so there weren’t a lot of extra features added around maintaining it.

Denny Cherry
 64,520 pts.