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 bySELECT 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?