Increase value of an identity column in SQL Server without dropping and recreating the table

You can use following Transact-SQL command

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

If you pass single parameter as:

DBCC CHECKIDENT
(
   table_name
)

will check the current identity value for the specified table in SQL Server:

Example

DBCC CHECKIDENT(tbl_test)

Output

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

Following will insert new identity value for the identity column:

DBCC CHECKIDENT
(
  table_name , RESEED, new_reseed_value
)

Example

DBCC CHECKIDENT(tbl_test,reseed,116)

Output

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

DBCC CHECKIDENT (yourtable, reseed, 500)

This will make the next entry 501.