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 |
|
mahadevan.swamy
Starting Member
1 Post |
 Posted - 2010-09-30 : 18:43:17
|
| I have a question about the identity column.I have created a table with an identity column where id starts from 1 and increments by 1. In this table I have inserted 5 records. Now I chose to delete the 5th record. When I insert a new record, the id starts from 6 rather than 5. How do I make the id start from 5 instead of starting from 6? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
glendcruz
Yak Posting Veteran
60 Posts |
Posted - 2010-10-01 : 01:25:55
|
| Syntex:IDENTITY_INSERT[database,[owner.]]{table}{On|OFF}Eg - If you are inserting into the Products tableSET IDENTITY INSERT Products ONinsert into Products(id,Product)values(3,'wwwww')I hope this is what you need |
 |
|
|
lazycoder
Starting Member
12 Posts |
Posted - 2010-10-01 : 04:43:29
|
You can reset the seed value with DBCC CHECKIDENT statement. So for example after running:DBCC CHECKIDENT ('TableName', RESEED, 4)the new row will have 5 in identity column.It's good eg. if you cleared your database and want to reset the identity values. Don't use it after every delete.-----------------http://it.expertmonster.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-01 : 04:53:55
|
| Both of these suggestions (glendcruz & lazycoder) are not suggestions I'd recommend. Not as in "They don't answer what you asked for" but in that you wouldn't almost never want to do either of these in general use.Can you answer tkizer's question. Why do you need an unbroken sequence? In sql server 2005 you can generate a sequence using ROW_NUMBER() if required. Mucking about with your identity column (which I'm also guessing may be your primary key?) is not a good idea except in exceptional circumstances (generally when something has gone very wrong)Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
lazycoder
Starting Member
12 Posts |
Posted - 2010-10-01 : 05:36:23
|
quote:
Originally posted by Transact Charlie
Both of these suggestions (glendcruz & lazycoder) are not suggestions I'd recommend. Not as in "They don't answer what you asked for" but in that you wouldn't almost never want to do either of these in general use.
As i noted in my post, it's not for general use, but very usefull in some circumstances. Eg when you are moving your database to production and want to clear data. To give better advice, we would need the reason why are the gaps problem.-----------------http://it.expertmonster.com/ |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-01 : 05:39:02
|
quote:
Originally posted by lazycoder
quote:
Originally posted by Transact Charlie
Both of these suggestions (glendcruz & lazycoder) are not suggestions I'd recommend. Not as in "They don't answer what you asked for" but in that you wouldn't almost never want to do either of these in general use.
As i noted in my post, it's not for general use, but very usefull in some circumstances. Eg when you are moving your database to production and want to clear data. To give better advice, we would need the reason why are the gaps problem.-----------------http://it.expertmonster.com/
Yeah -- I wasn't trying to rubbish the suggestion, just trying to make the OP think so they don't just go "Ah ha! my problem is solved" and then go and do something really really stupid that they don't need to do and which will bite them later.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Identity Column