This article is very serious and I would like to explain this as simple as I can. SCOPE_IDENTITY() which is commonly used in place of @@Identity has bug when run in Parallel Plan. You can read my explanation of @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT in earlier article. The bug is…Read More
Question I received in Email : How to list all the columns in the database which are used as identity key in my database?
SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table
Following script will return all the tables which has identity column. It will also return the Seed Values, Increment Values and Current Identity Column value of the table. SELECT IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1 AND TABLE_TYPE = 'BASE…Read More