March 1, 2005 at 4:59 am
Is there anyway of changing the Incremental values in Identity column by using a script like Alter tAble ...
My Blog:
March 1, 2005 at 5:14 am
I might be wrong, but I don't think there is an easy for this in an existing table. I would guess the way looks like.
- add a new column (tempcol)
- update that column with the identcol values
- drop identcol
- create new identcol with the incr value you want
- identity insert tempcol into new identcol
- drop tempcol.
Depending on the size of your table, it might be more convenient to do this with EM.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 2, 2005 at 12:52 pm
Checks the current identity value for the specified table and, if needed, corrects the identity value.
DBCC CHECKIDENT
( 'table_name'
[ , { NORESEED
| { RESEED [ , new_reseed_value ] }
}
]
)
/////////////////////////////////////////////////////////////USE pubsGODBCC CHECKIDENT (jobs, RESEED, 30)GOMarch 2, 2005 at 2:40 pm
the RESEED only change the last / next value not the increment therefore you have to follow Frank's suggestions
HTH
* Noel
March 2, 2005 at 6:19 pm
Just an FYI, but the seed value is just that...as seed that is used the first time only. The increment is always applied to the last id generated, no matter what it is. So, for example, if you wanted to only have odd numbers, this would not work:
if object_ID('tempdb..#Seed') is not null drop table #Seed
create table #Seed (ID int identity(1,2))
insert #Seed default values
insert #Seed default values
set identity_insert #Seed on
insert #Seed (ID) values (4)
set identity_insert #Seed off
insert #Seed default values
select * from #Seed
Generally this would not be a problem, as the actual ID assigned to an identity column should not matter (in an ideal design). What problem are you trying to solve?
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply