Dealing With Changing Data

A fairly common problem or need you encounter is managing changes of data
stored in a table. Sometimes you simply need to be able to see that data has
changed, other times you need to know when it changed, how many times it's
changed etc. This article will discuss a couple of ways of handling issues like
this.

Requirements and functionality

First of all you must establish what the requirements are. Which data type to
use and how to use it depends on the requirements and how you want to implement
the necessary functionality. The simplest, and probably most common requirement
is to be able to check if the data has changed since you last checked (i.e. when
the data was read into an application). With this functionality you are able to
avoid having different users overwriting each other's data changes. Script 1
below show's an example of how things can go wrong if this is not handled.

Another way of handling this requirement is of course to use pessimistic
concurrency. This means that data is locked as soon as it is read and no other
user is able to read that data until the locking user releases it. The SQL
Server way of doing this is of course to use Update Locks instead of Shared
Locks when reading data. Normally though you don't want to lock data like this
until you are actually going to update it so that other users don't have to wait
for you to release the data, especially if it is not even sure that you will
change the data. The alternative is of course called optimistic concurrency,
which means that all users can read the same data. To avoid overwriting each
other's changes you instead check before updating that the data in the database
haven't been changed by someone else since you read it. There are several ways
to do this and the simplest way is also the most automatic one, but we'll wait
with that for last. Instead we'll start with some other variants.

Last changed

A common solution is to use a column of some date type. This column will hold
the date and time when the data on a row was last changed (i.e. either INSERTed
or UPDATEd). The actual updating of this datetime info is either handled
'manually' by the application that updates the database, or automatically by
triggers in the database. Script 2 shows an example of how to hande it with
triggers which is normally the way I would handle it, mainly because as a DBA I
want to make sure that the data in the database is always correct and up to
date. If the application is left to handle this there is always a possibility
that some other application (present or future) will not handle it and then you
will not have correct data.

Timestamp

With the trigger in Script 2 we are now able to check when a row was last
changed. With this information we can now implement functionality to make sure
that User B is not able to overwrite the data that User A has already changed.
Again, the functionality can either be placed in the application where the
changes are done, or we can do it in the database with either a trigger or a
procedure. Since I like to restrict users from having direct access to tables
and instead giving them procedures to use Script 3 shows an example of a
procedure that updates the data, if it has not changed since it was read by the
user.

This technique is often called timestamp. By always retrieving the current
timestamp that the data has when SELECTing it, it is possible to check whether
the data has changed prior to updating it by comparing the timestamps, thereby
applying optimistic concurrency. If the row has a different timestamp than it
had when it was retrieved the user will have to SELECT the new version of the
data (including the new timestamp that is now the current one), decide whether
or not he still wants to do the change and if so try and update again.

A simpler stamp

If the requirements don't state that you actually need to see the specific date
and time when a row was changed but just need to see that it has been changed it
might be better to use an integer as a kind of simple stamp. When the data is
first INSERTed into the database it gets a default value (0 or 1 or whatever)
and then the trigger adds 1 to this each time the row is changed. In order to
update a row the user must supply the value that the stamp had when the data was
read, and then the procedure checks that the row does not have a different stamp
before updating it. The effect will be the same as using a datetime stamp, but
instead of being able to see when the row was changed it is instead possible to
see how many times it has been changed. It is only a matter of what the
requirements are. Also, note that an integer takes less space than a datetime
and it is impossible to have a 'collision' (two updates at almost the same time
could perhaps get the same timestamp), but you can of course run out of values
with the integer. I doubt that the same row will ever be changed more than
2,147,483,647 times though.

Rowversion

Very often though you don't need to know when the data was changed nor how many
times it's been changed. The only requirement is to make sure that the users
don't overwrite eachother's data changes. The simplest way to handle this is to
use the functionality that SQL Server has for this requirement, namely the
rowversion data type. Unfortunately this data type was earlier called timestamp
in SQL Server, which is unfortunate since it is not at all the same thing as the
timestamp that is described in the ANSI-SQL standard. The reason it was called
timestamp is because it existed in SQL Server before timestamp existed in the
standard. For backwards compatibility reasons it is still possible to use the
name timestamp for it, but just as most other things left only for backwards
compatibility in SQL Server it is recommended to use the new name rowversion
because the definition of timestamp might be changed in a future version of SQL
Server to adhere to the ANSI definition.

A rowversion in SQL Server is actually a binary(8) (unless it allows NULLs, in
which case it is a varbinary(8)), which is automatically changed when a row is
INSERTed or UPDATEd. A table can only have one rowversion column and it is not
possible to update it manually, it is always handled by SQL Server
automatically.

Just like when using a datetime or integer stamp you use it by retrieving the
current value when SELECTing data and then comparing it against the one stored
in the database when you want to update the row. With a column of data type
rowversion in a table it is also possible to use SQL Server's built-in
functionality for optimistic concurrency. This means that you can SELECT data to
a recordset in a client application and handle it locally using for example a
data grid. When you're finished changing the data in the grid you connect the
recordset to the database again and use Update or UpdateBatch (to update several
rows at once). SQL server will now automatically check if the row(s) have been
changed between the time when they where read and the update by comparing the
values in the rowversion column. It is actually possible to use automatic
optimistic concurrency control without a rowversion column but in that case SQL
Server needs to compare all the current data of all columns to the old values
they had when the recordset was originally opened. In that case the recordset
must always store both the original data and the new data that has been changed
locally.

Summary

Managing changes to data in SQL Server is as you can see not very complex, but
it can be very important. In the multi-user web applications of today it is not
normally possible to lock data while working with it so instead you must use
optimistic concurrency. If it is not handled users risk losing work and time,
and as you know, time is money.

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.