| Author |
Topic |
|
ddasilva99
Starting Member
26 Posts |
 Posted - 2006-10-25 : 10:16:56
|
| Is there a way to reuse the identity value?Example, if I add a row - the system auto generates a ID of "5". If I delete the row and readd a new row- the system generates ID of "6".How do I get it to regenerate an ID of "5"?Thanks,Derek |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2006-10-25 : 10:46:04
|
| You should NOT worry about having sequential ID numbers. The ID number means something to SQL SERVER, you should not be caring about it.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
 |
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-25 : 10:53:40
|
| Don- thank you for your note.I want to use the ID as a "student id" I would prefer that the ID was sequential so that there arent gaps in the "student id"Any suggestions?Thanks,D |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-25 : 11:16:16
|
| Use dbcc checkident to reseed the identity. It is not a best practice to add business value to a surrogate key ...Jay White |
|
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-25 : 11:35:57
|
| good to know- thank you |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-25 : 13:11:28
|
| Brett- thank you for your link.Why is it bad to use the Identity value as a 'business value'? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-10-25 : 13:16:29
|
| As stated before a surrogate key (Identity) should not have any meaning other than to identify the row to access the data or to join to other tables.You are placing a business reason to the id, and as you found out, this could get sticky, more so than just worrying about gaps. What you should do is keep the identity, and create another column for the "ID"Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
ddasilva99
Starting Member
26 Posts |
Posted - 2006-10-25 : 13:30:49
|
| Awesome feedback- thank you |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-28 : 02:59:36
|
| Even if you do that you will "lose" ID numbers if the transaction is rolled back.But then the other good folk here already said "don't use IDENTITY if you want contiguous numbers"Kristen |
|
|
|
|
Reuse Indentity Value