Hi,

I wanted to know if theres a fast way of knowing which columns in a row have
changed.

I have 2 versions of rows-old and new.
My table is as follows

CPK compositePrimaryKey---combination of 3 keys
versionID uniquidentifier 
param1 int
param1 char(10),
param3 bit
--etc

my new and old records are as follows

old version
----------------------------------------
composite primary key   guid    param1  param2     param3
   CPK1                      GUID1     4          'enabled'      0
   CPK2                      GUID2    5           'disabled'      0
   CPK3                      GUID3     6           'enabled'     1

new version
----------------------------------------
composite primary key   guid    param1  param2     param3
   CPK1                      GUID1     10          'enabled'       1
/*changed record*/
   CPK2                      GUID2     5           'disabled'       0
   CPK3                      GUID3     6           'disabled'       0
/*changed record*/

I am using binary_checksum(*) to find which ROWS are new ie they have
changed but I need to know which COLUMNS in each of those rows have
changed.There are about 100 columns in each row out of which only 3-4 will
change .
I am using SQL Server 2000.

Is there a way to extract just column names from each row that has changed?

Thanks.

cooltech77 (cooltec***@discussions.microsoft.com) writes:
No, you need compare column by column.

And using binary_checksum is risky. Two versions of the same row could
have different checksum.

Are you doing this in a trigger? Is the purpose for auditing? Which
version of SQL Server are you using?

thanks for the reply.i m using sql 2000 and i m not doing this in a trigger.I
am doing it in a procedure and all these calculations are done in a temp
table for the purpose of auditing changes to a column.


Show quoteHide quote

cooltech77 (cooltec***@discussions.microsoft.com) writes:
OK, you still need to run column by column. And avoid binary_checksum
or checksum.

Had you been on SQL 2005, I could have suggested alternative solutions.

Hi Erland,

I am using SQL Server express edition now.Can you please suggest your
alternative solution for finding changed row and columns?

Thanks.

Show quoteHide quote

cooltech77 (cooltec***@discussions.microsoft.com) writes:
I think that what I had in mind was to store an xml document of the
row in a table. This is possible, because in SQL 2005, you can use
FOR XML and receive the result in a variable or column of the xml data
type.

The audit table would look something like this:

   CREATE TABLE audits (
       ident     int         IDENTITY,
       tablename sysname     NOT NULL,
       keyvalue1 sql_variant NOT NULL,
       keyvalue2 sql_variant NULL,
       moduser1   sysname    NOT NULL
          CONSTRAINT default_audit_moduser1 original_login(),
       moduser2   sysname    NOT NULL
          CONSTRAINT default_audit_moduser2 SYSTEM_USER,
       moddate    datetime   NOT NULL
          CONSTRAINT default_audit_moddate getdate(),
       hostname   sysname    NULL,
          CONSTRAINT default_audit_hostname host_name(),
       afterimage xml        NULL,
       CONSTRAINT pk_audit PRIMARY KEY (ident))

Thus, one table would hold the audit for many tables. The idea is that
if you could write a general application that reads all rows for a certain
table and keyvalue. The application would compare the XML documents, and
the present just the difference. The beauty of it is that the application
itself would not need to have any knowledge of the data model as such.

Some notes on details:

o   The table only handle two-column keys. The assumption is that if you
    have more columns in the key, they could still pass as the same record.
    But add more keyvalue columns as needed.

o   SYSTEM_USER and original_login() returns the same value in most cases,
    but if EXECUTE AS is in force, they can yield different results.

Erland Sommarskog wrote:

Show quoteHide quoteErland,

I think you meant to say that two *different* versions of a row could
have the *same* checksum.  You're correct to say that checksums are not
completely reliable indicators of change.

Steve Kass
Drew University

Show quoteHide quote

hi Thanks Erland and Steve for all your replies.

We would be migrating to SQL 2005 soon.Can you please tellme how to do it in
SQL 2005?

Thanks.

Show quoteHide quote

cooltech77 (cooltec***@discussions.microsoft.com) writes:
One thing I was thinking of was hash_bytes(), which should be more reliable
than binary_checksum. But I forgot that it does not operate on the entire
like binary_checksum(*) does, so it's probably not that useful.

However, what is an interesting option for auditing on SQL 2005, is the
XML datatype. The idea would be to have a single table where you would
have tablename, keyvalue1 and keyvalue2, and an after-image of the data
in an XML column. Then you would have regular audting columns as
when, whom, application and host name.

The trigger code can be standardised and you could write a simple
application that reads the log row a combination ot tablename and keyvalue,
and presented you for each entry what have canged. What's really cool
here, is that the presenting application would not need have much
knowledge about the data it presents.

This is an idea that we discussed in our shop when a prospective customer
asked for better auditing than we have today. The customer dropped out
for other reasons, but we might implement the idea when we come to
SQL 2005.

Of course, this method would not be very space-effective, but we prefer
this since developing and maintaining space-effective auditing is
tedious and expensive.

Querying for nearest values...
Wish List: String Concatenation Aggregate Operator
How to count rows ?
BUG or not?
Norwegian collation