UPDATE question

Question

UPDATE question

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Jeff Moden (11/26/2009)
(sys.sysColumns I believe... not sure because this is 2k8).

Just sys.columns

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PiMané SSChampion Points: 13965 More actions · Answer 3

Thanks,

I'll try the column by column compare and see how it performs.

These kind of tables, the structure ones, have very little write... lot's of read before they go to app cache, and after that they are stalled...

Just another question, don't know if it makes any sense but...

Is it possible to, within the trigger, send the inserted and deleted values to MSMQ in a message so the trigger would be assync?

Thanks,

Pedro

If you need to work better, try working less...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Not so much MSMQ, but Service Broker sure. Question is, are you willing to put up with the massive increase in complexity and increased administrative overhead (and it is high) to get a little bit more performance? Is update performance that critical and that bad?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PiMané SSChampion Points: 13965 More actions · Answer 5

Thanks for clearing things out... 😀

I guess the best way is to have a historical table and put the deleted row on that table and when comparison is needed get the actual version and the desired historical version and compare on the app.

The trigger would be simple, just an INSERT INTO table_history SELECT deleted.*, GETDATE() FROM deleted and get all the rows from the history table to a certain date and compare with the actual version.

This is more a kind of safeguard to our side since we have customers saying "I didn't do that. It's probably a bug on your app the removed the field..." or something like this...

With this table we can always compare all the versions of the data and give a detailed report on what was done...

Pedro

If you need to work better, try working less...