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 (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
9 Comments. Leave new
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
Hi Pinal
this DBCC CHECKIDENT (‘Person.Address’) will give the following result
Checking identity information: current identity value ’22’, current column value ’22’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
and this can not be used in any query to get the output in any programmin language, so i think your above query is not worth.
Hi
Doesn’t the 2nd query also reset the identity if there is Gap between identity & Seed value?
AS SAID on MSDN
DBCC CHECKIDENT(‘Table Name’) or DBCC CHECKIDENT(‘TABLENAME , RESEED)
means?
I meant that above both syntax will reseed the identity if identity is lower than max identity used