dbcc checkident behaviour !!! 1

[miq]

hie,
while inserting test data identity key values get out of order like : 2,3,4,5,48,49,50,51,52,53,54,55,....

How can i rearrange identity key value to its original format i.e. seed = 1 , increament =1
so, it would look like : 1,2,3,4,5,6,7,8,9,.....

Is it possible ? I thought dbcc checkident will do it but it just set base for the upcoming data.

Taskpad view for database doesn't update. It as smoothly as it should. Is their any stored procedure,function or system table that can show me the exact size of transaction log and its ratio of used space.


Bye
miq

 

[tlbroadbent]

There is no SQL statement or query that will resequence the identity columns for you. There is no need for that kind of command. An identity column will always end up with sequence gaps. You could spend a lifetime resequencing identities. If you need to keep numbers in a sequence then create your own code for doing so and don't use identity columns.

Now that I've said that, you can resequence by dropping the identity column, and then adding the identity column again. SQL Server will simply increment the column value. Note however, that there is no guarantee of order. Rows may not be sorted in the same order when ordering by the identity column before and after this change.

Use DBCC SQLPERF (LOGSPACE) to see log space used. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[miq]

hi,
Great thanks for the lovely informative post.

really ranked you very high!!

Bye
miq