Notes from Pluralsight course - SQL Server Questions and Answers
In the Pluralsight course "SQL Server Questions and Answers" , Pinal & Vinod debunk some SQL Server misconceptions with examples and highlight uncommon facts - Use SCOPE_IDENTITY() instead of @@IDENTITY or IDENT_CURRENT("tablename") to get the latest IDENTITY value for the table in the session @@IDENTITY returns the last IDENTITY value produced on a connection SCOPE_IDENTITY() returns the last IDENTITY value produced on a connection (explicity created by you rather than a trigger) and by a statement in the same scope IDENT_CURRENT("tablename") returns the last IDENTITY value produced in a table, regardless of the connection Identity column value can be positive or negative Identity column can be reseeded with DBCC CHECKIDENT command Deleting records from table using DELETE does not reset Identity values whereas deleting records from table using TRUNCATE resets Identity values. TRUNCATE statements are logged and can be part of transaction. Such