Versioning in SQL Tables – how to handle it?

What you have here is called a Slowly Changing Dimension (SCD). There are some proven methods for dealing with it:

http://en.wikipedia.org/wiki/Slowly_changing_dimension

Thought I’d add that since no one seems to call it by name.