How to apply an identity property to a field in a table that has data

Question

How to apply an identity property to a field in a table that has data

Aspet Golestanian Namagerdi

SSCarpal Tunnel

Points: 4444
More actions
December 31, 2008 at 12:50 pm

#96872

Hi guys,
we want to take out the Identity property in a large table for a project,which we do not have any problem with that.The problem is that,in case that we need to rollback(take back the Identity property),we need to do it as fast as possible.So I can not rely on creating new table ,transfer the data to it....
So, I need a way to work directly with system tables(I guess it is SysColumns)
I did this the fallowing:
--Enable Identity Field
sp_configure 'allow update', 1
go
reconfigure with override
go
update syscolumns set colstat = 1,autoval=0x01000000010000000100000003
/*turn on bit 1 which indicates it's an identity column */
where id = object_id('table1')
and name = 'Code'
go
exec sp_configure 'allow update', 0
go
reconfigure with override
go
and when I tried
DBCC CHECKIDENT ( 'table1' , RESEED , 10 )
It says 'table1' does not contain an identity column.

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Are you in 2000 or 2005? You just asked the same question in the other forum.

What's important is the version # of your database engine. Whether you happen to be running in 80 compatibility is not relevant on this one......

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 2

I am working in SQL SERVER 2000,Because we do not have access to system tables,so I thought I have put in wrong forum,causer of that I add it here

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Well the script looks reasonable to 2000, except that I don't have a clue what it is you're doing to Autoval. can't say I have had to play with that when using that particular trick in 2000.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Aspet Golestanian Namagerdi SSCarpal Tunnel Points: 4444 More actions · Answer 4

well,base on my research it seems that sql server 2000,use autoval field for keeping Identity Increment & Identity Seed information.Unfortunetly I can not find proper value for this field, and also any explaination about this field

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 5

When you say rollback, what do you mean? DBCC should allow you to reseed the identity value. If you get that message, then I would guess the identity property is not set.

Are you use Identity_insert is not on? That's the only thing I can think of that would affect that.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

This is why we warned you that you shouldn't mess with systems table data directly... but if you insist on taking a chance especially without doing the necessary research (as you said)... 😛

There's a whole lot more that needs to change than just those couple of columns. Create two tables with just 1 integer column each... make the only difference the table name and the fact that 1 will have an identity and the other not. Compare [font="Arial Black"]all [/font]of the columns in SysColumns between the two table columns. Also, realize that the AutoVal column should also contain at least the current maximum value that is in the column to keep from having a conflict.

Even once you get all those requirements down pat, there's no real guarantee that your actions won't mess something else up further down the line because you (nor I) know what ALL the internals of SQL Server are. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

If you enable the identity property - why not just use DBCC CHECKIDENT to set the next value? That's the right way to do that, AND it's the supported way to do it.

If you're disabling the identity property, according to your description, autoval is irrelevant.

In short - don't screw with AutoVal....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?