Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dr223
Constraint Violating Yak Guru
444 Posts |
 Posted - 2009-10-09 : 07:58:31
|
| Hallo, I have a table with 3 fields - pay_id, pay_name and account_num. pay_id is set as Primary Key and is automatically incremented when a record is added. Now, when you delete a record(s), and add another record the pay_id remembers the last number (even if deleted) and increments to create the new pay_id. For example, I had the records belowpay_id pay_name account_num 2 John 7836434 3 Ali 34734434 James 8243245If the last record is deleted (pay_id = 4), the next new record will have a pay_id of 5 as shown below;pay_id pay_name account_num2 John 78364343 Ali 34734435 Julius 2752457Notice, pay_id is 5 rather than 4. Therefore I have two problems; 1) I want to amend this so that when a record is deleted then it reads the most present last record and increment the pay_id..2) My table (tblpay) pay_id's are so random due to deletion of records and creating new records. What will be the easier way of having pay_id's start from 1 onwards in an incremental fashion... Thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-10-09 : 08:19:21
|
| As this is an identity field by the looks of it, look up DBCC CHECKIDENT().You may need to get the max() curent number and add 1 to it if you really want them in order though, and in this case you have two worries, contention and overhead. Look into these further before deciding thiks is really what you want to do. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2009-10-09 : 09:46:16
|
| whats the issue with pay_id's being random? |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-10-09 : 10:28:52
|
quote:
Originally posted by X002548
WHY?Brett
I'd agree with Brett on this one, why on earth would you want to do this, and what possibly could be an advantage of doing this.Also what happens if someone deletes pay_id pay_name account_num2 John 7836434are you then going to re-increment all the entries and issue all the people with their new payid.I'd either leave it as it is or think of something different--------------------------SQL Server MCP & MCTS |
|
|
|
jason97m
Starting Member
4 Posts |
Posted - 2009-10-12 : 04:07:59
|
| I would recommend doing a date stamp each time a record is entered into the table. That way, you can order by date to show the most recent records in ascending order, regardless of what your primary key value is.CheersJason MorrisBRIXTON.US |
|
|
|
|
|
|
|
|
Increment Primary Key