SQL Server : compare columns in same table

I have a SQL Server table with following columns:

  • Clientnum
  • Location
  • Benefits
  • SomeOtherFields

And I need to be able to compare all the rows for a particular client and see which columns were changes/modified.

My usual approach is to concatenate all the columns for each row of the client and compare the concatenated string.

But somehow I know there could be a better approach (and besides my approach doesn't really tell me which columns were changes and before/after values).

Any suggestions would be helpful. Please note that I can not make any DBA level changes like track changes etc. Thanks a lot.

Answers


An option to accomplish this is to create a history table, and create Insert, Update, and delete triggers. Then you can query the history table to find all the details that you needed.

sample code:

CREATE TRIGGER [dbo].[uTests] 
   ON  [dbo].[Tests] 
   AFTER DELETE,UPDATE
AS 
BEGIN
	
	SET NOCOUNT ON;

    -- Log updated/delete record
	INSERT INTO [dbo].[Tests_TransHistory]
		(ID
	
		,[Test_Name]
	
		,[TestDate]
	
		,[UpdateDate]
		,[UpdateUser]
	
	SELECT 
		ID
	
		,[Test_Name]
	
		,[TestDate]
	
		,[UpdateDate]
		,[UpdateUser]
		
	FROM DELETED;
	
	--Update the time stamps.
	IF NOT EXISTS(SELECT * FROM INSERTED)
	BEGIN
		UPDATE dbo.Tests_TransHistory
		SET UpdateDate = getdate(), UpdateUser = current_user
		WHERE TransID = SCOPE_IDENTITY();
	END
	ELSE
	BEGIN
		UPDATE a
		SET a.UpdateDate = getdate(), a.UpdateUser = current_user
		FROM dbo.Tests a, INSERTED b
		WHERE a.ID = b.ID
	END

END
‚Äč

If you can't enable Query Notifications and use the SqlDependancy Class...

You could add a column of type ROWVERSION to your table. Every time a row is updated, SQL Server updates a column of type ROWVERSION with a new unique varbinary(8) value. Using this you can check if the ROWVERSION column changed at all and skip over those rows. If they don't match, then you can check the fields in only those rows individually.

Another option would be to use the HASHBYTES function to detect changes. If your client pulls down a view... you could add hashed values in your select. Something like:

select *,
  HASHBYTES ('MD5', Clientnum) ClientnumHash,
  HASHBYTES ('MD5', Location) LocationHash,
  HASHBYTES ('MD5', Benifits) BenifitsHash,
  HASHBYTES ('MD5', SomeOtherFields) SomeOtherFieldsHash
 from Table

Then you can just compare the results of the hash functions, comparing varbinary(16) (max lenght of the MD5 method) instead of the entire strings of the columns.


Need Your Help

What is this shell doing?

c shell

I was presented with this code as an example of how to make a 'mini shell' but I have no idea what it is doing-- I understand most things in the main function, but the functions above it initializi...