June 20, 2008 by pinaldave
Many times we need to know what is the current identity of the column. I have found one of my developer using aggregated function MAX() to find the current identity.
USE AdventureWorks
GO
SELECT MAX(AddressID)
FROM Person.Address
GO
However, I prefer following DBCC command to figure out current identity.
USE AdventureWorks
GO
DBCC CHECKIDENT ('Person.Address')
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Follow @pinaldave
Send +Pinal Dave
an email at pinal@sqlauthority.com
hi,
i tried it but it gives me error.
but when i am just giving the table name it gives the correct result
i think it should be like this
USE Name_Of_The_DB
GO
DBCC CHECKIDENT (‘Name_Of_The_Table’)
GO
please check as when i given the column name it gives me error
What is Person.Address???
i didnot get it plz help………..
even SELECT IDENT_CURRENT(‘table name’) gives the desired results
or we can use @@identity
@@Identity will not work. it will give the the last identity inserted in a transaction. i ll go with krishna.
@@Identity should never be used. Even IDENT_CURRENT() is not recommended, since other transactions can insert rows in the meantime. Use SCOPE_IDENTITY() to retrieve identity value of a row just inserted. If there is more than one row, use SCOPE_IDENTITY() in compination with OUTPUT clause to collect all ID’s. Using sequence objects in Denali is another possible option. Here is complete example for OUTPUT clause:
DROP TABLE t
CREATE TABLE t(x int IDENTITY(1,1), y int)
DECLARE @TableOfInsertedIds TABLE ( id int )
INSERT INTO t( y ) — inserts into “t”
OUTPUT INSERTED.x INTO @TableOfInsertedIds( id ) — this populates our table variable
SELECT TOP 10 object_id FROM sys.all_objects
SELECT * FROM t
SELECT * FROM @TableOfInsertedIds