Changing a Non-IDENTITY column to IDENTITY and vice versa

  • Thomas-428301

    SSC Veteran

    Points: 279

    Comments posted to this topic are about the item Changing a Non-IDENTITY column to IDENTITY and vice versa

  • Ian Yates

    SSCoach

    Points: 19738

    Good tip 🙂 I must admit that I'll still probably stick with letting the GUI tools do all the ugly scripting for me as most of our customers don't have SQL Enterprise (and obviously aren't running developer).

  • jdoherty-609236

    Newbie

    Points: 9

    I am on the business analyst side.....are you speaking of de-ideintification or masking.....?

  • Steve Reich

    SSC Veteran

    Points: 263

    Clever use of partition switching!

    Note that BOL warns that the transfer could introduce duplication in the identity column and recommends running DBCC CHECKIDENT (presumably after the switch).

    I recommend further reading BOL's article "Transferring Data Efficiently by Using Partition Switching" also available online in

    Technet: http://technet.microsoft.com/en-us/library/ms191160.aspx

  • JJ B

    SSCarpal Tunnel

    Points: 4905

    I appreciated the idea in this article.

    I thought I would also share another option. I routinely re-create my databases from scratch when I need to make major schema changes to the database. This identity change would count as such a change. The steps are:

    1) make changes to the data model as needed

    2) forward engineer a new database that is just like the original db only it has the schema changes you want.

    3) run all the code (stored procs, triggers, etc.) on new db

    4) run some code that automatically creates the code for a stored proc which copies table data from the source database into the new database

    5) tweak the copy code as needed and run it

    >> At this point, the new database is identical to the old database except for the changes you have deliberately made. So:

    6) backup the new database and restore over the original database.

    The advantages are that you follow the same set of steps every time. You don't have to say look for the set of indexes and foreign keys that are specifically attached to the one table you are working with. Also, you can make a whole bunch of minor and major changes to your database at once with the same set of steps. One of my favorite advantages is that you make sure your database matches your data model, and you know that your source code (stored procs, functions, etc.) are all up to date. In other words, you know that you can re-create the schema for the production database as needed at any time. The parallel is like always having a fully working build at your fingertips as application developers try to do.

    All of the steps above have to be done when no one is using the database. Thus, this option may not be practical for larger databases and longer up-times. I have relatively small databases (under a gigabite) and up-time is only 7-6 on weekdays. So, it is easy for me to have "down time" and work on weekends. For other agencies in my situation, this is an approach to think about for any type of significant schema change--and it works in any version of SQL Server.

  • Sean Zhang-369248

    Valued Member

    Points: 54

    Thomas,

    I like the technique with partition switching. Thank you.

    BTW, for your method 2, you will need

    SET IDENTITY_INSERT Destination_TB ON;

    before the insert.

    \Sean

  • dr.kusnadi

    Say Hey Kid

    Points: 696

    Hi Thomas,

    isn't just opening 'design table' from SQL management and edit the column to have identity or not, works as well?

    or it might be just me missing the point.

    Cheers,

    DK

  • Ahmad Osama

    SSCertifiable

    Points: 7676

    Hi,

    Can we have two identity columns on a single table?

    If not then how can one insert an identity column in a table having an identity column?

    Regards,

    Ahmad

    Regards,
    [font="Verdana"]Sqlfrenzy[/font]

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Daniel R. Kusnadi (2/21/2008)


    Hi Thomas,

    isn't just opening 'design table' from SQL management and edit the column to have identity or not, works as well?

    or it might be just me missing the point.

    Cheers,

    DK

    I have to agree with Daniel. The article is well written and well thought out, but in most case where there are no complicating factors it seems easiest just to use design table. Now, as can be seen by generating the change script, what this does in effect is very similar to method 2 listed, but in most cases it makes sense to let the gui handle that.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • solidsnake10_7

    SSC Rookie

    Points: 44

    doesnot work with me nothing affect the identity of the table dont get off

  • the sqlist

    SSCrazy

    Points: 2398

    SQL Frenzy (2/25/2008)


    Hi,

    Can we have two identity columns on a single table?

    If not then how can one insert an identity column in a table having an identity column?

    Regards,

    Ahmad

    The answer is simple: you can only have 1 identity column per table. If what you want with the second part of your question is to move the identity attribute from one column to the other, personally I don't see why one would do such a thing as you already have an identity, then first you have to turn off the identity on your original column and then add another column with identity attribute.

    As per article, the best way to go is in the Enterprise Manager or Management Studio in design mode on the table, switch on the identity attribute on the column, copy the SQL that was generated and then save it in a file.

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • WILLIAM MITCHELL

    SSChampion

    Points: 13697

    I have to agree with the previous posters, using the GUI to generate the change script is the best approach. The column being changed stays in the same ordinal position, the script runs inside a transaction, and preserves all the indexes, constraints etc.

    You can't have users in the db when you're making this type of change, so you can't avoid having at least some downtime.

  • ChiragNS

    One Orange Chip

    Points: 26137

    Good tip.

    "Keep Trying"

  • Kavin-1064454

    SSC Veteran

    Points: 230

    Thank you very much Mr.Thomas. I came across the same issue ie non- identity to identity type change and your article has helped a me a lot.

    But when I have tried it in a temp database it shows the error

    Msg 245, Level 16, State 1, Line 1

    Conversion failed when converting the varchar value 'TMP' to data type int.

    But again I have tried in another database its working fine.

    --drop table tmp

    create table tmp

    (s_no int,

    name varchar(10)

    )

    insert into tmp

    values

    (12,'name1')

    insert into tmp

    values

    (13,'name2')

    insert into tmp

    values

    (14,'name3')

    ALTER TABLE tmp ADD s_no_II INT IDENTITY(1,1)

    ALTER TABLE tmp DROP COLUMN s_no

    EXEC sp_rename

    'tmp.s_no_II','s_no', 'COLUMN'

    Can u brief and how the error can be overcome?

    Thanks in advance.

  • JeremyG

    SSC Enthusiast

    Points: 193

    I have to respectfully disagree with the posters that are saying that using the GUI is the best approach. If by best you mean easiest, then that would be true. But if you look at what is involved, the article really dives into an interesting and super-efficient way to accomplish the task. If you had a 120gb table (as I have had), and you do this through the GUI (or script what the GUI would do and run it), you would find your users very displeased at the amount of downtime, assuming you dont run out of diskspace while your query creates a copy of the table with a new identity column. This is what the GUI does, if you wanted to add an identity to a table without one (in 2005)...

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_SourceTable

    (

    Id int NOT NULL IDENTITY (1, 1),

    Column1 varchar(50) NULL,

    Column2 varchar(50) NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT dbo.Tmp_SourceTable OFF

    GO

    IF EXISTS(SELECT * FROM dbo.SourceTable)

    EXEC('INSERT INTO dbo.Tmp_SourceTable (Column1, Column2)

    SELECT Column1, Column2 FROM dbo.SourceTable WITH (HOLDLOCK TABLOCKX)')

    GO

    DROP TABLE dbo.SourceTable

    GO

    EXECUTE sp_rename N'dbo.Tmp_SourceTable', N'SourceTable', 'OBJECT'

    GO

    COMMIT

    If i didnt have another 120gb on my disk, i would not be able to do the above based on diskspace alone.

    Very nice and very interesting article.

Viewing 15 posts - 1 through 15 (of 28 total)

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