none
How to drop identity property of Column RRS feed

  • Question

  •  

    Hi all,

     

     

    Can any one help me or guide me how to drop identity property of a column with help of smo or TSQL ?

     

     

    Any help is appreicated.

    Friday, November 9, 2007 11:34 PM

Answers

  • You can not remove Identity propery of a column with out droping it. Possible method will be

     

    (a) Add a new column

    (c)Update the column with identity column value

    (c) Remove the identity column.

     

    Alter Table Tablename Add newColumnname int

    Update Table set newColumnname =IdentityColumn

    Alter Table TableName Drop IdentityColumnName

     

    Note : If you are talking about switching off the identity property for time being, check Set IDENTITY_INSERT TableName ON Command

     

     

    Madhu

     

    Monday, November 12, 2007 2:20 AM
    Moderator

All replies

  • You can not remove Identity propery of a column with out droping it. Possible method will be

     

    (a) Add a new column

    (c)Update the column with identity column value

    (c) Remove the identity column.

     

    Alter Table Tablename Add newColumnname int

    Update Table set newColumnname =IdentityColumn

    Alter Table TableName Drop IdentityColumnName

     

    Note : If you are talking about switching off the identity property for time being, check Set IDENTITY_INSERT TableName ON Command

     

     

    Madhu

     

    Monday, November 12, 2007 2:20 AM
    Moderator
  • Hi,

     

          It looks like its been a couple of months since you posted your query, without getting any results. I just had to figure this one myself. In my case, I needed to remove the identity property from an INTEGER column.

     

    Here's what I did:

     

                 ALTER TABLE [tablename] DROP CONSTRAINT [constraintname]

     

    Does this help?

     

    Cheers.....

     

     

    Monday, February 11, 2008 2:24 AM
  • My initial response didn't actually work - oops.  I only removed the Primary Key restraint.  That didn't actually remove the IDENTITY property from the column.  To remove the Identity property, I had to RIGHT-CLICK on the table in SQL Server Management Studio, select Design and highlight the column I wanted to change. Then, I went to the Column Properties and scrolled down to Identity Specification and changed (Is Identity) to 'No.'

     

     

    Monday, February 11, 2008 3:19 AM
  • Hi

       i want to remove Identity Property through Sql Query not using of Sql Server Management Studio.

     

    Tuesday, April 29, 2008 7:39 AM
  •  Sachin_08 wrote:

    Hi

       i want to remove Identity Property through Sql Query not using of Sql Server Management Studio.

     

     

    Check the post marked as answer. You can't do that using TSQL. In Management studio when you do this it internally do the following steps create new temp table /copy the data/drop the old table/rename the temp table

     

    Madhu

    Tuesday, April 29, 2008 7:44 AM
    Moderator
  • I know it has been some time after you posted you question but still....

     

    ***********************

    sp_configure 'allow update', 1
    go
    reconfigure with override
    go


    update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
    where id = object_id('table_name') and name = 'column_name'
    go


    exec sp_configure 'allow update', 0
    go
    reconfigure with override
    go

    ***********************

    Julia
    • Proposed as answer by Kevin_0711 Thursday, December 2, 2010 8:41 PM
    Thursday, June 26, 2008 11:49 PM
  •  

    HI all whoever are searching for this problem

     

    This can help

     

    Allows explicit values to be inserted into the identity column of a table.

    Syntax

    SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

    Arguments

    database

    Is the name of the database in which the specified table resides.

    owner

    Is the name of the table owner.

    table

    Is the name of a table with an identity column.

    Remarks

    At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

    If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

    The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

    Thursday, July 17, 2008 2:24 PM
  •  Kiran Kumar Uppuluncha wrote:

     

    HI all whoever are searching for this problem

     

    This can help

     

    Syntax

    SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }Allows explicit values to be inserted into the identity column of a table.

     

     

    Kiran,

    I think you have miss read the thread. The command which you are mentioning is to switchoff identity property. THe question was  how to drop identity property of a column probably using SMO. Even the earlier post (which recommend system table update will not work in 2005 and its not recommened either in 2000.

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Saturday, July 19, 2008 3:00 PM
    Moderator
  • I found one way only - recreate table and copy old data to a new table or recreate column. Any way, if you have billion rows in your table, it will be like the ____.

     

    .NET Developer

    Thursday, April 29, 2010 8:20 PM
  • I know it has been some time after you posted you question but still....

     

    ***********************

    sp_configure 'allow update', 1
    go
    reconfigure with override
    go


    update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
    where id = object_id('table_name') and name = 'column_name'
    go


    exec sp_configure 'allow update', 0
    go
    reconfigure with override
    go

    ***********************

    Julia


    This solution works fine for me.
    Simple, fast and didn't change the timestamp if you have a column of this kind (that's what I was looking for...)

    Kevin.

    Thursday, December 2, 2010 8:47 PM
  • Well i think the answer is provided couple of times above; you have two ways either create new table or Add one new column copy the data into that column and drop the identity column;

     

    the soulution provided by Kevin above is not suitable for 2005 and above version as Microsoft dont allow to uupdate in the system tables.

     

    Cheers

     

    Talib

    Friday, June 24, 2011 10:28 AM

  • Hi,
    I have one ETL Package where I roll back data from Destination to Source. To do so, I disable all constrains on tables by using ALTER TABLE "Table_Name" NOCHECK CONSTRAINT ALL on begenning and at end I enable all constrains on tables by using ALTER TABLE "Table_Name" CHECK CONSTRAINT ALL.

    For few tables, I have IDENTITY column. My question, when I use ALTER TABLE "Table_Name" NOCHECK CONSTRAINT ALL, do it disable IDENTITY column Constraint also?

    Please clarify it.


    Thanks Shiven:) If Answer is Helpful, Please Vote
    Monday, October 10, 2011 6:03 AM
  • Hi,

    If in OLE DB Destination Editor (Connection Manager Page)

    Data access mode = Table or view – fast load Or Table name or view name variable – fast load,

    If you check option "Keep identity" then you can map a IDENTITY column. Keep identity: Specify whether to copy identity values when data is loaded. This property is available only with the fast load Or Table name or view name variable – fast load option.

    The default value of this property is false (Unchecked) which means you can not map IDENTITY Column.

    In your ETL Package If you have checked option "Keep identity" and once load completed then you want to enable IDENTITY Column properties then you need to execute "SET IDENTITY_INSERT Schema_Name.Table_Name OFF".

    For Example:

    This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

    -- Create products table.
    CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
    GO
    -- Inserting values into products table.
    INSERT INTO products (product) VALUES ('screwdriver')
    INSERT INTO products (product) VALUES ('hammer')
    INSERT INTO products (product) VALUES ('saw')
    INSERT INTO products (product) VALUES ('shovel')
    GO
    
    -- Create a gap in the identity values.
    DELETE products 
    WHERE product = 'saw'
    GO
    
    SELECT * 
    FROM products
    GO
    
    -- Attempt to insert an explicit ID value of 3;
    -- should return a warning.
    INSERT INTO products (id, product) VALUES(3, 'garden shovel')
    GO
    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT products ON
    GO
    
    -- Attempt to insert an explicit ID value of 3
    INSERT INTO products (id, product) VALUES(3, 'garden shovel').
    GO
    
    SELECT * 
    FROM products
    GO
    -- Drop products table.
    DROP TABLE products
    GO
    

     


    Thanks Shiven:) If Answer is Helpful, Please Vote

    Monday, October 10, 2011 6:43 AM
  • Hi,

    -- Create products table.


    CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))


    GO

    -- Inserting values into products table.
    INSERT INTO products (product) VALUES ('screwdriver')
    INSERT INTO products (product) VALUES ('hammer')
    INSERT INTO products (product) VALUES ('saw')
    INSERT INTO products (product) VALUES ('shovel')

    -- SET IDENTITY_INSERT to ON.
    SET IDENTITY_INSERT products ON
    GO

    -- Attempt to insert an explicit ID value of 3
    INSERT INTO products (id, product) VALUES(3, 'garden shovel').

    SET IDENTITY_INSERT [ database. [ owner. ] ] { table }  ON = can insert an explicit ID value

    SET IDENTITY_INSERT [ database. [ owner. ] ] { table }  OFF = can not insert an explicit ID value


    Thanks Shiven:) If Answer is Helpful, Please Vote
    Monday, October 10, 2011 6:56 AM
  • No, This doesn't disable the IDENTITY on the column.

    You have to explicitly turn it ON/OFF

    Thursday, January 5, 2012 6:17 AM
  • Hi Julia,

    It's kind of an old post, but I'm hoping is still relevant. I'm trying to do the same on a SQL 2008 server and getting 

    Ad hoc updates to system catalogs are not allowed.

    am I missing something?


    Mordechai Danielov

    • Proposed as answer by donna_123_a Friday, May 10, 2013 8:47 PM
    • Unproposed as answer by donna_123_a Friday, May 10, 2013 8:47 PM
    Thursday, May 2, 2013 6:32 PM
  • Looks like you have to drop the identity property via SSMS manually.  However, while doing this in SSMS, you can script out the SMO commands so that you can repeat it again later with just the SQL... which will basically drop and recreate the table.  Here's the post for that:

    http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/

    Friday, May 10, 2013 8:49 PM
  • here is the quick shortcut to remove the identity property

    http://www.calsql.com/2012/05/removing-identity-property-taking-more.html


    Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

    Wednesday, May 29, 2013 1:46 PM
  • Tried this:::
    sp_configure 'allow update', 1
    go
    reconfigure with override
    go
    update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
    where id = object_id('ATT.DimProviderMarkets') and name = 'ProviderMarkets_key'
    go
    exec sp_configure 'allow update', 0
    go
    reconfigure with override
    go

    got this error msg:::
    Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
    Msg 259, Level 16, State 1, Line 1
    Ad hoc updates to system catalogs are not allowed.
    Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

    /****/
    basically tried a simple test table with id as int with identity constraint.
    inserted 1 value into it.

    Then tried this sp to delete and got same error.
    Tried via clicking way and got a msg suggesting that I have to drop and recreate the table to remove id constraint.

    :::: I was basically trying this on a table that has 13M records; due to identity being changed to sequence constraint due to requirement change in my project.
    only way I did it was drop the table and re-create with sequence implementation.. and re-run the job via SSIS/job scheduler(s) way.
    Thanks,
    Rai.

    Tuesday, October 8, 2013 9:44 PM
  • Hi

    this does not work in sql server 2008 r2 and it is not a good practice.

    you can refer to this link below:

    http://www.sqlservercentral.com/Forums/Topic466853-149-1.aspx

    regards

    M

    Thursday, February 13, 2014 5:42 PM
  • Hi,

    Please check below link to Remove IDENTITY property from a column in a table by handling Foreign key relationships in both AZURE and On-premises-

    https://arulmouzhi.wordpress.com/2019/11/11/to-remove-identity-property-from-a-column-in-a-table-by-handling-foreign-key-relationships-in-both-azure-and-on-premises/

    Friday, January 17, 2020 3:03 PM