SQL Server has a built-in data type that automatically marks changes to rows. This data type can be used for 2 main purposes: optimistic record locking and data queue processing.
A while ago I did a talk that was a very general overview of many of the popular software development processes being used in the industry. Following the talk I released a large list of additional resources. Go forth learn. Here is that list.