T-SQL - DBCC CHECKIDENT

Set a column in a table as IDENTITY will guarantee to have unique value in that column which increment by inserting new records, then such a column is a good candidate to be PRIMARY KEY., but the question is, how to reseed the value or find what's the latest value now?

A table with a column which is set to IDENTITY can take advantage of DBCC CHECKIDENT.
The syntax for this command is as following:

DBCC CHECKIDENT ('table_name'[,{NORESEED |{RESEED[,new_reseed_value]}}])

• NORESEED returns back the current identity value for the column.
• RESEED with a value which comes after it with a comma, sets the column to a specific value and increment for the next inserted rows with predefined IDENTITY increment.

Example 1:
DBCC CHECKIDENT ( '[FBtst].[dbo].[Product]', NORESEED )

Output message:
Checking identity information: current identity value '101', current column value '101'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Example 2:
DBCC CHECKIDENT ( '[FBtst].[dbo].[Product]', RESEED, 200 )

Output message:
Checking identity information: current identity value '101', current column value '200'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Find more here:
http://technet.microsoft.com/en-us/library/ms176057.aspx

T-SQL - DBCC CHECKIDENT