Wednesday, September 29, 2010

Blind Spot - T-SQL $Identity

I've been working with SQL Server for 12 years, but this short cut escaped me.

In Transact-SQL, the $IDENTITY keyword allows us to refer to the identity column without knowing its name. Since identity columns are often primary keys, we could use this as a short cut for always referring to the PK. This would be awesome to use in a code generator or an ORM.

This is not to be confused with @@IDENTITY or SCOPE_IDENTITY(), which tells us the actual number auto generated by the IDENTITY column (though each behaves in different ways.)

Test it out with this script:

use tempdb
go

create table test
(
id int identity(1,1)
, descr varchar(100)
)
go

insert into test
(descr)
values
('first record')
, ('second record')
, ('third record')
go

select $identity, descr from test

4 comments:

  1. Replies
    1. Great Article Cloud Computing Projects

      Networking Projects

      Final Year Projects for CSE

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. These are really a superb showcases here I am glad to have a review of it and feel to roll on its steps for my benefit always.jogos friv gratis 2019
    Jogos 2019
    jogos friv

    ReplyDelete