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.

Bill E.
Hollywood, FL

History/audit data is best kept in separate tables (and sometimes if
required can be even separate database).

A few cases may require the history data to be kept in the same table. For
example, in bill of material for parts you can keep old versions of the part
in the table. But history there is managed via a pair of start/end effective
dates which define the latest active part, and prior versions. This allows
to query the part to point of time and see how it was at particular date.

Also, using active/inactive flag only is not meaningful. It doesn't help to
see when this version was active and when it became inactive. Keeping a
period of active/inactive dates is normally a lot better and allows to
answer very easy queries like "What was the active version of this item by
that date?".

Partitioning could be another approach, but not sure applicable in your
case.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Plamen,

Thanks for responding.

In fact, I have stored audit tables in a separate database on one
occasion. Also, I'm currently working on a database project where we
are storing contracts. We are going to keep contract pricing terms in
a way very similar to the way you handled your bill of materials--with
a start and end date. In this way, users will be able to store/view
contract terms for the same product/service as they apply today and
also as they applied in the past and will apply in the future.
However, I don't think of this as an "audit" function in the strict
sense because we are not really tracking changes to records. For
example, if someone enters pricing terms for a future date range and
then changes the price, I am not going to have a record of it.

One thing that I have to ensure is that overlapping date ranges for
the same product do not occur. I'm wondering how you handled this.
For example did you create a constraint in your database, did you
place a check in an update/insert trigger, did you handle it in a
stored procedure when the user entered a new part version or some
other approach?

Bill

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

Bill E. (billmiami2@netscape.net) writes:
> One thing that I have to ensure is that overlapping date ranges for
> the same product do not occur. I'm wondering how you handled this.
> For example did you create a constraint in your database, did you
> place a check in an update/insert trigger, did you handle it in a
> stored procedure when the user entered a new part version or some
> other approach?

I would typically do this in a trigger. Here is a sample from a table
that I have:

CREATE TRIGGER pgm_ins_upd_tri ON pledgegroupmembership
FOR INSERT, UPDATE AS

IF EXISTS (SELECT *
FROM inserted i
JOIN pledgegroupmembership pgm ON i.accno = pgm.accno
AND i.startdate <> pgm.startdate
WHERE NOT ((i.startdate > coalesce(pgm.enddate, '99991231') OR
coalesce(i.enddate, '99991231') < pgm.startdate)))
BEGIN
ROLLBACK TRANSACTION
EXEC ael_handle_sp @@procid, 17, 'ERR_AEL_BOSINTERNAL',
'Internal error: overlapping intervals for the same account in pledgegroupmembership.'
RETURN
END





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

I normally prefer to handle the checking for overlaps (as well as the update
of the end date for the old occurrence) via a trigger. A few times clients
requested this to be done in the stored procedures handling the data update
(with the argument that somebody may disable triggers).

When archiving data in a way you to do not have to worry about overlaps.
Since the system automatically sets the date and time based on the system
clock, overlaps should not occur unless the system clock if not accurate. On
systems where the user sets the dates it is important to enforce the
checking.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Plamen and Erland,

Thank you for your responses and suggestions.

Bill E.
Hollywood, FL

>> 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 [sic: this confuses records
and rows] one of which is active. <<


>> I've never used this approach. <<

You are not old enough. Back in the early days of disk files, when we
still mimicked magnetic tapes and pre-allocated files with fixed or
variable length records, there would be a "deleted/active" bit flag at
the start of each physical record. The file system could set these
flags faster than it could over-write an entire record. Eventaully,
there were enough deleted records in a file that the system would run
a garbage collection routine (COMPRESS, COMPACT, DEFRAG or some such
name) to get rid of them, sort the file and re-index it.

>> I typically create a separate table to capture change history. In my experience, change history is rarely if ever accessed. <<

No, change history is done in a Data Warehouse,not an OLTP system.
What you want is an audit tool that gets this information from the log
file. Think about what a crash will destroy; think about how easy it
is to doctor audit trails for one person (instead of the two or more
required by law) with system access; now think about Feds coming to
visit.

>> My concern is that this database will eventually handle millions of customers and these tables will contains millions of "dead" records [sic:rows are not records] SQL Server will need to sift through. It is true that we'll be having indexes on important columns (i.e., customer_id) so perhaps I shouldn't worry about it. <<

Worry about it. It will hurt performance in a fast changing table.
YOu can often update a row in place; his kludge requires a disk write
for the flag and a disk write of the entire row.

>> Nevertheless, I wonder whether this approach is good practice. <<

It sucks