Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 4027
Home > Forums > Sql Server >

sql auto sequence order

Posted by 0194ca071007 under Sql Server on 1/5/2012 | Points: 10 | Views : 2290 | Status : [Member] | Replies : 7
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
hi

i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g

1 - entry
2 - entry
(3 - deleted no mere here)
4 - entry
5 - entry

It will be highly thnkful



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Sksamantaray on: 1/6/2012 [Member] Silver | Points: 25

Up
0
Down

What the problem if row 3 is deleted?
you are unable to generate 6?
you are unable to view records in sorted manner?

if you donot want to delete record and view the record to user , then maintain a delete status.For that you create another column deleteStatus and keep default value=0, while user deletes the record , physically donot delete , just update the deleteStatus value=1

I think i have clarified your doubt, else kindly explain your problem in detail.

Thanks,
Sanjay

0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Avdesh on: 1/6/2012 [Member] Starter | Points: 25

Up
0
Down
What u want for that ?Please clarify your Question?

Thanks Regards

(Avdesh)

0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: 0194ca071007 on: 1/6/2012 [Member] Starter | Points: 25

Up
0
Down
i want to be my records in sequence order as 1,2,3,4,
when 3 number of record is deleted, 4th number record shuld automatically change in 3 number record
r u clear now.........................................


0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Avdesh on: 1/6/2012 [Member] Starter | Points: 25

Up
0
Down
Yes it will arrange automatically but if you create column(ID) by urself and you will delete it then it will not arrange as it's your custom data

0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sksamantaray on: 1/6/2012 [Member] Silver | Points: 25

Up
0
Down

suppose id=3 is to be deleted
After deletion update table set id=id-1 where id>3

use this logic in delete procedure

Thanks,
Sanjay

0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Hmanjarawala on: 1/6/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi,

SK gives right reply, but my question is its didn't need to maintain this type of sequence in any situation...
for example...consider a case where you've 1000 of records and want to delete 1st record....then what..for that pertuicular deletion will you fire update for rest records......

i think its not needed in any case

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

Up
0
Down 
SET IDENTITY_INSERT tablename ON


INSERT into Tablaname (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...) 


SET IDENTITY_INSERT tablename OFF



Hope This will help

Regards


sriram

0194ca071007, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts