custom auto-generate ID - help

[chesnutsy]

Hi,
I know that when you set a column in ms sql to auto-generate IDs you won't be able to change the values. i'm having a problem with that restriction, because i have a table called Requisition, and for each entry, the requisition has a revision number. I've set the Requisition ID to auto-increment
so, requisition 1 rev 1 --- thats the primary key for each entry. when i would like to add a requisition 1, rev 2 mS sql does not allow me, and it just adds a new entry as Requisition 2 rev 2.
should i not use auto-increment? and should i just create my own auto-generation sequence? -- i really don't want to do that, is there any way that i could modify the RequisitionID column from time to time?
Please guide me to the right path.

Thanks very much,
Francesca

[Ramy Mahrous]

In your case don't use [auto number] rather generate it yourself in your front end application.

[chesnutsy]

I was thinking more in the line of sequences. Before when we were using Oracle DB, we used sequences that generated the IDs for us. we'll us the sequence whenever we want to increment the ID, otherwise, we'd just increment the revision.
is there something like this in MS SQL?

Thanks!

[Ramy Mahrous]

Yes, check auto number column properties. but I also prefer to do the generator on your application side.

[max_krauzer]

with se(id)
as (
select 120 as id
union all
select (id+1) 
as id from se
where id<(120+4)
)
select * from se