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.
tech77 (tech77@.discussions.microsoft.com) writes:> 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?
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?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.
"Erland Sommarskog" wrote:
>
tech77 (tech77@.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?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||
Erland Sommarskog wrote:
>
tech77 (tech77@.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.
>
Erland,
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
>Are you doing this in a trigger? Is the purpose for auditing? Which
>version of SQL Server are you using?
>
>|||
tech77 (tech77@.discussions.microsoft.com) writes:> 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.
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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.
"Steve Kass" wrote:
>
> Erland Sommarskog wrote:
>
> Erland,
> 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
>
>|||
tech77 (tech77@.discussions.microsoft.com) writes:> We would be migrating to SQL 2005 soon.Can you please tellme how to do
> it in SQL 2005?
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
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I am using SQL Server express edition now.Can you please suggest your
alternative solution for finding changed row and columns?
Thanks.
"Erland Sommarskog" wrote:
>
tech77 (tech77@.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.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||
tech77 (tech77@.discussions.microsoft.com) writes:> I am using SQL Server express edition now.Can you please suggest your
> alternative solution for finding changed row and columns?
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, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment