Docs and blog posts describe what row version columns do, but rarely delve into the decision process of when it's appropriate to use them. I suspect that many developers just add them to every table without considering whether they're actually needed. This saves development effort, but it comes at a cost that I may want to avoid.
What are the use cases for SQL row version columns? When should they not be used?
(There's another kind of row versioning that I understand to be related in purpose but unrelated in implementation. This question is not about that.)
Perhaps the clearest case for row versioning is when an entity has a property with read-increment-write semantics. In favor of row versioning everywhere, you could argue that any property could be used this way, and this usage might be difficult to foresee. Even a boolean property could have read-modify-write semantics if the intention is to toggle it rather than set it.
At the other extreme, if all of an entity's properties are independent and "last write wins" semantics are acceptable, then row versioning seems pointless.
I've used APIs where entire entities had to be read and written to change a single property, even when that property was independent. This made update conflicts frequent, so these APIs relied heavily on row versioning. In that scenario, I view row versioning as a necessary evil mitigating a bad API design.
On the other hand, if update DTOs contain only changed properties to merge or coalesce, and those properties are independent or groups of dependent properties are sent together, then row versioning might be unnecessary. Or maybe a row version could exist but be optional in the DTO, making it the client's responsibility to include it if it wants safe read-modify-write semantics.
From the perspective of API design, another consideration is that adding a row version column would be a breaking change. But then again, so would adding columns or business rules that make row versioning necessary where it wasn't before. This goes back to the effort of thinking about whether row versioning is needed. Maybe the cost of using it everywhere is worth eliminating the risk of failing to recognize where it's necessary.