This came up during a discussion about meta data for table rows on the Readify internal mailing list.

Wouldn't it be nice to have a way to logically delete rows in a table, i.e. make it so that they were available for looking up information like descriptions and so forth but in general did not come as results in normal select statements.  It would be even better if obsolete rows could be excluded when trying to establish new foreign key relationships.

Here's the feedback that has been placed on the SQL 2008 CTP site:

SQL Server could have:

OBSOLETE FROM sometable WHERE someclause

and those rows would no longer be returned by

SELECT columns FROM sometable

unless you also added:

SELECT columns FROM sometable WITH OBSOLETE

Rows could be reinstated by:

REINSTATE FROM sometable WHERE someclause

Foreign key relationships could then be defined as not permitting new references to obsolete rows. No doubt performance gains could also be obtained where the engine knows which rows won't be updated. Part of this could be done by partitioning today but it's not the full story.  

 

What do you think?

P.S. If you are on the SQL 2008 CTP program then go to the Microsoft Connect site and vote for the Obsolete/Reinstate feature via this link.