May 2023 Discount: Comprehensive Database Performance Health Check | Testimonials

SQL SERVER – 2008 – SCOPE_IDENTITY Bug with Multi Processor Parallel Plan and Solution

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
Database, SQL Error Messages, SQL Identity, SQL Index, SQL Scripts
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

SQL SERVER – Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Error Messages, SQL Identity, SQL Scripts
Error Message: Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF. This error message appears when you try to insert a value into a column for which the IDENTITY property was declared, but without having…
Read More

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Function, SQL Identity, SQL Index, SQL Scripts, SQL Server, SQL System 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