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)
Pinalkumar Dave is Microsoft SQL Server MVP, Solid Quality Mentor and a prominent author of over 1000 SQL Server articles at SQLAuthority. He is a dynamic and proficient Principal Database Architect, Corporate Trainer and Project Manager specializing in SQL Server Programming with over 7 years of hands-on experience. He holds a degree in Masters of Science and has accomplished a number of certifications including MCDBA and MCAD (.NET). He has also been awarded Regional Mentor for PASS Asia.
pinaldave
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.