While it is convenient to have SQL statements generate values, it can be problematic if the generated values need to be known and used. For example, it is a widespread and common practice to define a table using IDENTITY to populate primary key values during INSERT statements. Sometimes, there is a need to know the IDENTITY value immediately after an INSERT statement is completed perhaps, to do INSERTs into child tables. Additionally, when tables have default values set by functions such as GETDATE(), there are times when these values need to be known after the INSERT. Similarly, an UPDATE statement can explicitly set a columns new value to a value known only at execution time (e.g., setting the current date and time to GETDATE() or calculating the value of a computed column).
The article continues at