i have an primary key who generated itself when i tried to insert some data, but the problem is it will be generated the last one who i deleted before
I wan to make an input when i input the new data the generated id will be 17 not 72 and so on, i'm using SQL SERVER 2008, help :{
...but the problem is it will be generated the last one i deleted before
Do not expose your primary key. It's a bad idea.
Primary keys are internal unique row identifiers. They are not supposed to be sexy or good looking. If you are caring about its value, then it means you somehow want to expose its value to the external world, and that's a really bad idea; if you do this you'll be converting this key into a natural key somewhere else in a different application and that can become an expensive dependency to maintain in the short, medium, and long term.
Solution? If you really care about the format, numbering, and you want to expose the PK, then create a secondary key [column] on the table. This one can be generated automatically, with any value or format you prefer. And you can feel free to expose it to any other application, or even to the end user.
You can set IDENTITY_INSERT ON on the table and insert 17 and corresponding values. Read Identity_insert on MSDN. Also make sure that there should not any conflict occuring in future due to existing identity setup.
In that, you would have to reseed the identity.
You cannot update the identity column. You have to insert it fresh.
Sample code below
create table #table (id int identity(1,1), name varchar(30)) insert into #table values('A'),('B') SELECT * FROM #TABLE SET IDENTITY_INSERT #table ON insert into #table(id,name) values(8,'C') SET IDENTITY_INSERT #table OFF SELECT * FROM #table +----+------+ | id | name | +----+------+ | 1 | A | | 2 | B | | 8 | C | +----+------+ 
72based on the screenshot in your question.ROW_NUMBER()when selecting from your table.