Identity Column

cvishu · Active User Joined: 31 Jul 2007 Posts: 136 Location: india

I have a requirement where a particular column needs to have a sequence number generated for each row inserted.

I am planning to use the IDENTITY COLUMN to automatically generate the sequence.

My doubt is , what happens when a particular row is deletd and a new row is inserted ?

Ex :

Identity_Column data1 data2
1 XX1 yyya
2 XX2 yyyb
3 XX3 yyyc
4 XX4 yyyd

If i delete the row 3 will the Sequence number of xx4 yyyd still remain as 4 or will it be decrementd to 3?

Also if sequence 4 is not going to be decremened , now if i try to insert a new row will it take a sequence id 5 ? or will it take the sequence id 3 as its no longer available ?

Do let me know if am not expressing myself clearly

Thanks

enrico-sorichetti · Posted: Thu Nov 04, 2010 11:56 am

why not test it Yourself ???
a couple of spufi statements will solve Your doubt

cvishu · Active User Joined: 31 Jul 2007 Posts: 136 Location: india

Err the problem is we dont have access to CREATE a table .. and as of now i dont find any existing tables tat have the IDENTITY Column .. so basically am helpless.

I need to go through lot of DBA approvals to get a table created.. and for testing am damn sure those DBA wont let me create a table

Thanks

enrico-sorichetti · Posted: Thu Nov 04, 2010 12:17 pm

cvishu · Active User Joined: 31 Jul 2007 Posts: 136 Location: india

yup but sadly i am only allowed to propose the design ....

GuyC · Posted: Thu Nov 04, 2010 3:21 pm

"Identity-column or not" is not part of the logical model. It's technical.

But to answer your questions :
1) No, other rows are unaffected by delete of a row.
2) identity columns keep incrementing (normally by 1) but one can specify other increments (even -1), one can also cycle after maximum is reached , but then you might get duplicates.