Home > SQL Server, T-SQL > SQL SERVER – SCOPE_IDENTITY (T-SQL) – Identity Column @SQLSERVER

SQL SERVER – SCOPE_IDENTITY (T-SQL) – Identity Column @SQLSERVER


SCOPE_IDENTITY(): This function returns the latest identity value inserted into an identity column in a table. As given in the function name, this function returns the output with in the scope. The scope is a code boundary such as a stored procedure, trigger, function, or batch.

Function Syntax

SCOPE_IDENTITY()

This is function does not have argument. We can use this function in an expression or anywhere in the scope. But, This function should be executed after an insert statement in the scope otherwise it will return a null value.

Example

1. Executing function without insert statement

select SCOPE_IDENTITY() as ‘Current Identity’

pic12. Executing this scope_identity() with insert statment

I have already created a table with the following structure

CREATE TABLE [dbo].[tblIdentity](
[RowID] [int] IDENTITY(1,1) NOT NULL,


[varchar](10) NULL
) ON [PRIMARY]

pic2There are other functions that gives us this value with some limitations. I will discuss those function in the next blog post.

Advertisements
  1. No comments yet.
  1. April 29, 2013 at 10:45 pm
  2. April 29, 2013 at 11:27 pm

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: