Discussion on:

How do I... Reseed a SQL Server identity column?

I have a 16,449 rows table with a primary key which started seeded at 1 with increment of 1.
I add 2 rows to make it 16,451. Then I delete these 2 last rows I added.
dbcc checkident (table) yields,
Checking identity information: current identity value '16451', current column value '16451'.

I thought I should get current identity value '16451', current column value '16449'.

I apologize for the confusion because the terminology does imply what you've inferred. However, I checked with expert Francisco Tapia and he confirmed the following for me:
CHECKIDENT returns the last identity value, even if it's no longer in the table -- so that's what I meant by "current value." He suggests you take a look at scope_identity, although, again, it won't tell you if a record's been deleted.

Excellent Article

I have used IDENTITY often (much to the disappointment of Joe Celko), and have one question. How would you fill in holes in an IDENTITY column?
Say I have been using the column as a generic Primary Key generator for a BI application. Some old data gets deleted, foreign keys are also deleted, etc.

Now I have holes that I would like to reuse; can reseed be used to address this somehow? I???m wondering if the error produced by attempting to use an IDENTITY value that already exist could be caught in a TRY / CATCH process and re-attempted somehow. Any idea?

Personally, I wouldn't reuse deleted identity values. If some business rule requires a complete list of consecutive values you have a few choices:

1.) Rethink the business rule (my personal favorite). A lot of times these rules are unnecessary once you analyze their origin and use.
2.) Use a calculated column (not sure you can do this with a cc) or a sp to generate the numbers instead of using the identity property.
3.) Don't delete records. Flag them as deleted, but don't actually remove them. Accommodate them in your queries and views.

For more information on Identifying and listing all the IDENTITY columns present in a given database with filtering option available for specific tables, refer the below link:
http://www.sqllion.com/2011/08/identity-in-sql/

I had to execute the procedure twice for each table, first it set the current column and next time it set the current identity. I did it through Visual Studio though so that might have something to do with it.