Post reply

Confirmation for removing identity column

  • July 21, 2005 at 2:47 pm

    #88212

    Hi all,

    I know that we cannot use the ALTER table statement to change the identity column to integer column with no identity. The opposite is possible though.

    SQL Server 2000, Sp3a and SP4, win 2000, 2003 servers.

    I have to remove permanently over 800 identity columns... different servers, different databases, different locations... some of the tables are huge - like 2 bilions records...

    I planned to add a new column, to copy the data over from the identity one, to drop the identity and to rename the newly created column.

    To much pain...

    Does somebody have another idea? I'm hoping that somebody already found a way to do that easier way...

    Please, advise what are my options - is there a miracle???

    Thanks, mj

  • July 21, 2005 at 5:18 pm

    #576272

    Um, why?

    (!)

  • July 22, 2005 at 9:48 am

    #576479

    I do not appreciate such replays - we do not need to waist our time and the forum resources.

    Thanks.

  • July 22, 2005 at 10:35 am

    #576496

    I think you are in for some pain...  With 800 tables you might want to invest the time to create some SQL generating SQL to create the statements necessary. 

    But Merrill's question actually may not be as irrelevant as you first thought.  I too would question why you are wanting to make such a modification.  If you are still using surrogate keys IDENTITY columns are the most efficient way of generating them in SQL Server despite the protests of some that it is a proprietary function.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • July 22, 2005 at 2:17 pm

    #576564

    There's no DB objects in the application so it'll be db independant... The same code runs on Db2, Oracle, SQL server, etc...

    OK, I found the way - why nobody advised me for that?

    It's simple trick. Try this

    sp_configure 'allow updates',1

    reconfigure with override

    drop table ident_tab

    create table ident_tab(ident_col int identity, col2 int)

    sp_help ident_tab

    update sysobjects set status=1610612736 where name='ident_tab'

    update syscolumns set colstat=0 where name ='ident_col' and id in(select id from sysobjects where name='ident_tab')

    sp_help ident_tab

    insert into ident_tab values(100,10)

    It works like a charm...

    MJ

  • July 22, 2005 at 3:04 pm

    #576577

    why nobody advised me for that?

    Well what you are doing is undocummented!

    You say that it works like a charm but right off the bat I can tell you that you are improperely handling a bit map column (colstat)

     update syscolumns set colstat=0 where name ='ident_col' and id in(select id from sysobjects where name='ident_tab')

    Yes it clears bit "0" which effectively determines the identity "property" of the column in syscolumns but it is also clearing the rest of the bits!!! and... do you know what are the other bits for ?

    it will be more prudent to just affet the "required" bit only like:

    update syscolumns set colstat= colstat & ~1 where name ='ident_col' and id = object_id('ident_tab')

    This is a very undocumented procedure and I for one will not do it (or recommended) for a production system.

     

    Good Luck

     


    * Noel

  • July 22, 2005 at 4:52 pm

    #576591

    Thanks for correcting me - this will be definately the safer way.

    I'll try it this weekend on a middle table of 50 mln and I'll post my results on Monday.

    If Microsoft cannot offer something so simple with "documented" ways, we'll need to find out the back doors...

    thanks a lot,mj

Viewing 7 posts - 1 through 6 (of 6 total)

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