Reseeding identity columns

I recently worked on a project I which I redesigned sales data warehouse as a STAR schema, using daily file partitions, with a automatic sliding window, and applying data compression at the page level. I ended up reducing a 5 terabyte database to less than 750 GB. I will be writing several articles on the lessons that I learned during the process.

Today, I want to talk about how to reseed a identity column if a data load for a dimension fails. Most dimension tables use a surrogate key to uniquely identify each row in the table. In a logical world, we would like to have these values increase in ascending order without gaps.

I will be dusting off the [WILDLIFE] database from a prior article. This database has one table named [ANIMALS]. It has 445 unique animal names each with a unique identifier. Sounds awfully like a dimension table to me.

It is important to note that deleting from the end of the table does not have any effect on the sequence of the seed. The example below deletes 200 rows from the end of the table and then adds 5 rows back.

Notice that the next identity value is 446.

Five new records have surrogate keys from 446 to 450.

We are lucky that there is a DBCC CHECKIDENT command that can be used to view the next identity value and maximum column value. This same function can be used to reset the key. The code snippet below removes the five records that creates a gap in a sequence, reseeds the identity column and adds back the 5 records so that the sequence is unbroken.

Reseeding the identity column.

The five tail records are now in sequence order from 246 to 250.

The last thing to mention is that the TRUNCATE TABLE command automatically resets the identity column to the initial seed. The code snippet below truncates the ANIMALS table and add five tail records to the table.

We can see that the records were inserted with IDs 1 to 5.

In conclusion, data warehouse have dimension tables that contain surrogate keys. The DBCC CHECKIDENT function can be used to view and reseed identity columns. If it is important to your organization to maintain a unbroken sequence of unique identifiers in a dimension table, you should learn how to reseed a identity column.


Related posts

Leave a Comment