Results 1 to 8 of 8

Thread: Drop Identity

  1. #1
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Unanswered: Drop Identity

    Is there anyway to drop the Identity characteristic from a field? I have a database where I need to remove some Identity characteristics from the primary keys.

    I have been able to work around this situation by deleting the primary key column, and creating a new primary key without the Identity... but that seems the long way around.

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Use enterprise manager. Right-click the table and choose design table. Once in design view you can remove the identity.

  3. #3
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Thanks... but can I script it.

    Thanks for your reply. I should have qualified my initial problem. I knew I could remove the Identity with the Enterprise Manager, but I wanted to script the change so that it could be run on a couple of different remote servers by individuals other than myself.

    Any idea how to do that?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Thanks... but can I script it.

    You could make the changes as suggested above and then save them to a script file (instead of executing them). That's an option in SQL EM when you are in table design mode. I think in this case (though I'm not positive), that the script EM will generate will create a temp table (without the identity fields), copy all the records into the temp table, drop the original table and then rename the new temp table with the name of the original table.

    I could be wrong on the sequence, but I think that is what is required when eliminating the identity property from a column.

    Regards,

    Hugh Scott

    Originally posted by acg_ray
    Thanks for your reply. I should have qualified my initial problem. I knew I could remove the Identity with the Enterprise Manager, but I wanted to script the change so that it could be run on a couple of different remote servers by individuals other than myself.

    Any idea how to do that?

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: Thanks... but can I script it.

    Use EM the same way as if you remove identity in design table, but click SCRIPT instead of SAVE.
    Identity cannot be dropped without dropping column with identity. To free this dropped fixed column used space, you must drop table. So without dropping table you cannot remove identity and keep fast table. You need too many opperations to be done...

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    This is what ss does - your method is probably faster:

    BEGIN TRANSACTION
    SET QUOTED_IDENTIFIER ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    COMMIT
    BEGIN TRANSACTION
    CREATE TABLE dbo.Tmp_Test
    (
    ID int NOT NULL,
    Code varchar(10) NULL,
    Name varchar(255) NOT NULL,
    Conference int NOT NULL
    ) ON [PRIMARY]
    GO
    IF EXISTS(SELECT * FROM dbo.Divisions)
    EXEC('INSERT INTO dbo.Tmp_Test(ID, Code, Name, Conference)
    SELECT ID, Code, Name, Conference FROM dbo.Test TABLOCKX')
    GO
    DROP TABLE dbo.Test
    GO
    EXECUTE sp_rename 'dbo.Tmp_Test', 'Test'
    GO
    COMMIT

  7. #7
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    But you must drop and recreate all referenced objects, all those PK,FK,DF,CK,IX and other schema bound objects.

  8. #8
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Re: Thanks... but can I script it.

    Thanks... this will be of great help.

    - Ray


    Originally posted by hmscott
    You could make the changes as suggested above and then save them to a script file (instead of executing them). That's an option in SQL EM when you are in table design mode. I think in this case (though I'm not positive), that the script EM will generate will create a temp table (without the identity fields), copy all the records into the temp table, drop the original table and then rename the new temp table with the name of the original table.

    I could be wrong on the sequence, but I think that is what is required when eliminating the identity property from a column.

    Regards,

    Hugh Scott

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •