Just Learned - SQL Server - $identity returns the identity column details of a table in SELECT statement

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Just Learned

A collection of quick technology learning tips from what people around you learn every day


	Submit a tip


	Subscribe..

Home · Most Liked · Most Learned · Most Discussed · Silver Rated · Sections

SQL Server - $identity returns the identity column details of a table in SELECT statement

Jun 23 2012 12:00AM by Ramireddy

"SQL Authority" Pinal Sir published a tip about $rowguid, which can be used to fetch uniqueidentifier column from a table. It was awesome. You can find the link below.

SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables

Initially thought it's like a cheat code. But its about mastering the basics. In MSDN, if we analyze the syntax of "SELECT" statement, we can understand that $rowguid is part of options in select clause. I understood another trick from that syntax, retrieving identity column information without using column name. $identity can be used to fetch identity column information from a table.

Eg:

CREATE TABLE Companies
(
    Id int identity(1,1),
    Name varchar(100)
)
INSERT INTO Companies VALUES ('a'),('b'),('c')


SELECT $identity 
FROM Companies

6 Comments

Instead SELECT $IDENTITY FROM Companies , i would use SELECT $IDENTITY.Companies ( only because i am a little lazy ) Anyway, thanks to permit me to recall this tip

commented on Jun 24 2012 3:41AM

Patrick Lambin

162 · 1% · 296

May be you might want to check this post as well - http://vadivel.blogspot.in/2011/10/identity-in-sql-server.html

commented on Jun 24 2012 6:45AM

Vadivel

474 · 0% · 79

Patrick, what exactly is this "SELECT $IDENTITY.Companies" of yours?

commented on Jun 25 2012 2:03AM

dishdy

17 · 10% · 3262

According to http://msdn.microsoft.com/en-us/library/ms176104.aspx part $IDENTITY , but i recognize that it is not clear .So it is possible i have not understood what it is written "If more than one table in the FROM clause has a column with the IDENTITY property, $IDENTITY must be qualified with the specific table name, such as T1.$IDENTITY" If i have misunderstood what it was written, i hope you will excuse me.

commented on Jun 25 2012 7:02AM

Patrick Lambin

162 · 1% · 296

Great tip Rami...Thanks...

commented on Jun 25 2012 1:49PM

Robert Dennyson

11 · 14% · 4419

$identity is the replacement for IDENTITYCOL. IDENTITYCOL stands for Identity column name in a given table. Since IDENTITYCOL is being depricated from SQL Server 2012, It's time to place it with $identity in DML statements.

So going forward we should use

SELECT $identity FROM Customers (retruns the CustomerID column values)

Instead of

SELECT IDENTITYCOL FROM Customers

commented on Aug 23 2012 1:47PM

Madhu Gorthi

1978 · 0% · 7

Your Comment

Sign Up or Login to post a comment.

"SQL Server - $identity returns the identity column details of a table in SELECT statement" rated 5 out of 5 by 12 readers +Ramireddy