SQL Server Performance Forum – Threads Archive

data updates

Hi,
Using SQL 2005 if I have a table that needs updating when some other data gets changed, what is the best method for doing this? Is it a trigger or would it be using Notification services? I want to build a denormalised table but will also keep the original tables and when the data gets modiffied in the base tables I want these changes to be pushed out to the denormalised table. Thanks for any suggestions
Jamie..

How often the data is modified?
How big the database and update transaction would be?
If it is around 10% in a day then Trigger would suffice the need. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Also give us an example of the process you want to do Madhivanan Failing to plan is Planning to fail

Thanks for your replies. Essentially we have an end of day process which executes this view many thousands of times and this view is showing up as being rather slow. So I figure instead of executing the view all these times why don’t we denormalise the data. This is all well and good but what happens when the data is modiffied? I need to somehow update the denormalised data set. What other options are there to triggers.

Make this view indexed. Indexed view is actually a table that is automagically maintained on each base table data change.

I would have used an indexed view only there are some function calls and left outer joins. So due to the indexed view restrictions am unable to use this approach.

True and they have been optimized in SQL 2005 as compared to SQL 2000.
http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>