Posted on

Why You Should Avoid Using the @@IDENTITY Function

Phil Factor demonstrates why SQL Prompt has a 'Best Practice' rule (BP010) that checks for use of the @@IDENTITY function and suggests less error-prone ways to get the latest identity values used in a table.

The @@IDENTITY function returns the last IDENTITY value created in the same session. If you have a trigger on the table or if the table is a publication in a replication, then the value can sometimes be wrong. SQL Prompt's BP010 code analysis rule will warn you if it detects its use in your SQL code.

Posted on

Refactoring Databases With SQL Prompt

Louis Davidson demonstrates how SQL Prompt can significantly lessen the pain involved in occasional, 'heavyweight' database refactoring processes, such renaming modules, tables and columns (Smart Rename), or splitting tables (Split Table).

Many of the tools that SQL Prompt provides are ones that you'll use more or less every single day you write T-SQL code. The refactoring tools in SQL Prompt are more like the ones in the snakebite kit that you take on a long hike in the desert. You hope not to have to use them too often, but when you do, they are extremely valuable. My previous article focused on small scale code refactoring, limited to altering the 'internals' of a single batch or object. A less frequent but harder requirement is to change the 'public interface' of an object, such as by changing the name of an object, or column, or even by splitting tables to achieve a better design.