Can i drop identity type from a column???

  • is it possible to drop an identity type form a column without either dropping /rebuilding the table (as done in EM and MS) or drop / rebuild the column?

    is there just a simple alter statement that will drop this type from the column so i can assign a new column to have identity......?

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • If the column is Primary Key and other tables references it, no.

    otherwise

     

    ALTER TABLE Table1 DROP COLUMN

     


    N 56°04'39.16"
    E 12°55'05.25"

  • thanks for that peter but i don't actually want to drop the column itself, just the idenity from it.

    so just to clarify, i want to keep this column exactly as it is EXCEPT i do not want it to have identity insert on it as i want to add a new column to the table which will have an identity insert on it....

    i know i can drop the column or in fact the table and rebuild but was wondering if it were possible to drop the identity insert without doing that....???

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

  • Unfortunately you cannot drop the identity property with a simple alter table statement. You will need to rebuild this table (create another one with no identity property, copy the data, and move the table).

    You could actually use Management Studio, expand in the object explorer the table, and rightclick on the column (and set the Identity property to No). Not sure how well Management Studio does the rebuild, but in a simple testcase for me it preserved the data. Do make a backup

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • thanks for the confirmation Andras! i've tried it through MS (ideally going to be running it through a stored proc) and checked the change script and it basically creates a temp table with the new column set as identity, bulk inserts all the data from current table to the temp, drops current table and renames the temp to what the current table was.

    Just seems like a lot of work for what i thought would be a simple change but to be honest it works relatively quickly as the table is not too large....

    here is a cut down version of the script (uid is the new column i'm adding):

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_MyTable

    (

    uid int NOT NULL IDENTITY (1, 1),

    PrtNo nvarchar(20) NULL,

    PrtDesc nvarchar(70) NULL,

    etc

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_MyTable OFF

    GO

    IF EXISTS(SELECT * FROM dbo.MyTable)

    EXEC('INSERT INTO dbo.Tmp_MyTable (PrtNo, PrtDesc, etc)

    SELECT PrtNo, PrtDesc, etc FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')

    GO

    DROP TABLE dbo.MyTable

    GO

    EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'

    GO

    CREATE INDEXES.............etc

    GO

    COMMIT

    _________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

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

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