Bill E. (billmiami2@netscape.net) writes:
> I am working on an application development project where another
> developer is responsible for the design of the SQL Server 2005
> database. Instead of allowing inserts or updates, many important
> tables are designed so that "old" versions of records are maintained
> in the table and marked as "inactive" using a bit column. For
> example, if a user enters his address and then updates it five times,
> we will be left with six address records, one of which is active.
>
> I've never used this approach. I typically create a separate table to
> capture change history. In my experience, change history is rarely if
> ever accessed.
>
> My concern is that this database will eventually handle millions of
> customers and these tables will contains millions of "dead" records
> SQL Server will need to sift through. It is true that we'll having
> indexes on important columns (i.e., CustomerID) so perhaps I shouldn't
> worry about it. Nevertheless, I wonder whether this approach is good
> practice.

I would agree that this is not a design that I really like. Then again,
there may be business reasons for it that I don't know of.

One with it that I can see, is the code perform an update is simple:
mark the old row as inactive as when it happned and insert the new.
No need to do a separate insert to the history table. And if the
schema changes, you don't need to change the history table. Whether
these advantages are really worth it, I'mt not so sure.

In any case, I think you should add an indexed view which holds the active
rows, or else there can easily be bugs when programmers forgets to
include the "isactive = 1" condition in the query. At first glance your
colleague might find this attractive and argument for his idea. However,
there are some problems with indexed views. If you have:

select ... from indexedview where col = @val

SQL Server will first expand the view to the query, and then only use the
view in the query plan if both these are true: 1) it is able to match
the expanded query to the view 2) you are running Enterprise or Developer
Edition. You can circumvent this by adding the NOEXPAND hint, but then
there is one more thing that easily can be forgotten.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx