How to make indentity column continually?

Hello, everyone:

I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

Thanks a lot

ZYTThis works for me. I'm using EM. After you delete the rows from the table, remove the IDENTITY property and save the change to the table. Then add the IDENTITY property back using the defaults (seed and increment of 1). The next row that will be added should have the next available number as it's IDENTITY value. In your case, 32.|||1. Add a new column
2. Copy data from identity column to new column
3. drop identity column
4. change name of new column to old identity column
5. make new column an identity

BUT!

The fact that you want to do this tells me the column should not be an identity column.

You will forever more be worry about gaps in sequences, ect.

The identity column shouldn't be used for "ordering" data.|||Wow, Brett makes laws...

DBCC CHECKIDENT('table_name', RESEED|NORESEED, <new_seed_value>)

And why can't you order by identity column?|||And why can't you order by identity column?

I guess that didn't come out right...

They are artificially leaning on an IDENTITY Column where gaps in the sequence is a problem...if they are building a process that is dependant on the fact that there has to be a "next row", then I would consider that a bad design.|||A surrogate key, whether INT or GUID, should not be relied upon for ordering data. By definition it has no inherent relationship to the data it represents.

It's not a law. It's a principle, and a good one.|||It's not the relationship to data that defines the ordering, it's the characteristics of the field. In this case if the key is clustered then the order is dictated by the value of the field, not by what data type it is or whether it has a relationship to data or not. READ THE POSTS!|||Dude, I can see your veins popping from here. That can't be healthy.

Yes, a clustered index is physically ordered. Duh.

But it is a bad idea to depend on a surrogate key not having gaps, or even being an indication of the order the data was created. That's what the datetime datatype is for.|||That's not what the topic was about, READ THE POSTS! And leave my veins alone. I am not saying anything about what's popping on your face, right? ;)|||I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.

Read the post...ok...

Dude...they run out of tequila in Texas?sql