-1

I have a little problem.

I had 50 rows with identity column id- autoincrement. So, first row had id 1, last row had id 50. Then, i delete rows from 1 to 25 and now i have rows with id 26-50.

Question:

How could i "reinit" this rows (26-50) to (1-25)? I don't need empty id's in table. Thx.

SQL Server 2008

FSou1
  • 1,861
  • 4
  • 18
  • 25
  • 6
    I can't think of any valid reason for doing this. – Joe Stefanelli Oct 24 '11 at 16:35
  • okay, now we know what you think :-) – camainc Oct 24 '11 at 16:38
  • possible duplicate of [SQL Server - reset identity field](http://stackoverflow.com/questions/2107505/sql-server-reset-identity-field) – GSerg Oct 24 '11 at 16:38
  • In my query, i need to take row with random id and when i generate it, i need to make 2 subquery (select min(id), select max(id)). If i have from 1 to 25, i need to execute just one query (select max(id)). This is the reason... – FSou1 Oct 24 '11 at 16:39
  • GSerg, should it clean all rows from my table, or just reset next id when i add row? Or it should reinit my existing row's id? – FSou1 Oct 24 '11 at 16:41
  • 1
    You should not be using an identity column for this. If your key values have to relate to something in the real world, just use an INT and manage the values yourself. Identity columns exist to maintain unique values - that is all. – camainc Oct 24 '11 at 16:46
  • When ur target- the smallest execution time- every subquery/column could make it worse, even if there are to much rows in table. – FSou1 Oct 24 '11 at 16:50
  • @FSou1 - based on the SQL knowledge on display in this question, I have to assume there are dozens of other optimizations to be made without doing something silly like resetting the ID seed. – JNK Oct 24 '11 at 17:24

4 Answers4

4
DBCC CHECKIDENT('your_table', RESEED, 0)

The next value will be whatever you reseed with + 1, so in this case I set it to 0 so that the next value will be 1.

More details on http://msdn.microsoft.com/en-us/library/ms176057.aspx

Bala
  • 4,427
  • 6
  • 26
  • 29
  • Should it update my existing rows or just change start id when i'll add next row in table? – FSou1 Oct 24 '11 at 16:46
  • 1
    It won't update existing rows; it will just change the start id for next inserts – Bala Oct 24 '11 at 16:47
  • Technically, If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value. – Bala Oct 24 '11 at 16:49
  • yeaP, But i AskeD How TO update existing id's without delete or copy from tmp storage. – FSou1 Oct 24 '11 at 16:52
  • Check http://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically – Bala Oct 24 '11 at 17:00
4

I wouldn't recommend doing this at all.
The purpose of an identity column is not to generate pretty-looking numbers, but unique values to identify your data.
If the identity column is referenced in another table, you can't just change the values in your table without breaking the reference.

If you still insist on doing it:

You can "reset" the identity counter by using DBCC CHECKIDENT:
SQL Server - reset identity field

Note that you can't do this if there is still data in the table, as explained here.
You could work around this by copying the data temporarily to another table, resetting the counter and insert the data again, but this is not a very elegant way.

Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • I know this way, with copy to tmp storage and add after this with query/macro. But i thought, mb is it too easier solution. – FSou1 Oct 24 '11 at 16:48
0

I'm not sure why you are doing this, but it has a funny smell about it. If you are trying to return a row number I would suggest you use row_number and order by the id. E.G.

SELECT ROW_NUMBER() OVER(ORDER BY ID) AS 'ID', * 

FROM your_table

Ross

Ross Dargan
  • 5,876
  • 4
  • 40
  • 53
-1

EDIT - use this command to reinitialize an identity column.

TRUNCATE TABLE [tablename]

For what you are trying to do you cannot/should not use an identity column.

camainc
  • 3,750
  • 7
  • 35
  • 46