Home > SQL Server > SQL SERVER – IDENT_CURRENT – Identity Column @SQLSERVER

SQL SERVER – IDENT_CURRENT – Identity Column @SQLSERVER


IDENT_CURRENT : this function also returns the recently generated identity value in a table as SCOPE_IDENTITY and @@IDENTITY functions. But IDENT_CURRENT function does not have scope and session restriction to get the latest identity value.

This function requires an argument. This argument is mandatory and it should be a name of an accessible table by the user. This function may return null value if the user does not have permission to access the table object or passing invalid argument.

The return type of this function is numeric and the size is numeric(38,0).

Function Syntax

IDENT_CURRENT( ‘table_name’ )

Example

1. Executing this function to get the last inserted identity value of a table.

select IDENT_CURRENT( ‘[dbo].[tblIdentity]’) ‘Current Identity’
GO

pic32. This function returns the seed value if the table does not have any row yet or the table has been truncated. In this example, I am going to truncate the table and execute the function to see how it works?

SQL Query

Truncate table tblIdentity
GO

select IDENT_CURRENT( ‘[dbo].[tblIdentity]’) ‘Current Identity’
GO

Output

pic4This output is the seed value because the table is truncated.

IDENT_CURRENT() does not have any restriction such as scope or session. So, we should be careful in using this function to get the latest identity value. Because, other users may work with this table in another session.

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: