Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Using Built-in system functions – IDENT_CURRENT(), IDENT_INCR(), IDENT_SEED()

Using Built-in system functions – IDENT_CURRENT(), IDENT_INCR(), IDENT_SEED()


When an identity column is created, we need to specify the initial value and an increment value for the identity column.

You can retrieve the seed and increment values using IDENT_SEED and IDENT_INCR respectively.

— © 2011 – Vishal (http://SqlAndMe.com)

USE   AdventureWorks2008R2

 

SELECT      IDENT_SEED('Production.Product') AS 'Seed',

            IDENT_INCR('Production.Product') AS 'Increment'

Result Set:

Seed                                    Increment

————————————— —————————————

1                                       1

 

(1 row(s) affected)

IDENT_SEED function returns the 'original' seed value even if it has been changed using DBCC CHECKIDENT.

DBCC  CHECKIDENT ('Production.Product' , RESEED, 1001)

SELECT      IDENT_SEED('Production.Product') AS 'Seed',

            IDENT_INCR('Production.Product') AS 'Increment'

Result Set:

Checking identity information: current identity value ‘1001’, current column value ‘1001’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Seed                                    Increment

————————————— —————————————

1                                       1

 

(1 row(s) affected)

To retrieve the current identity value that was used we can use IDENT_CURRENT function.

SELECT      IDENT_CURRENT('Production.Product') AS 'Current Value'

Result Set:

Current Value

—————————————

1001

 

(1 row(s) affected)

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.