Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
surfandswim
4 Posts |
 Posted - 2008-10-22 : 20:16:52
|
| I have a few questions about what happens when an update statement is issued to SQL Server (2005).Let me setup the scenario. There is a table "Table1" with fields "PK" (uniqueidentifier primary key) and "Name" (nvarchar no index).If an update statement is issued where the PK is updated, but the value remains unchanged, does SQL Server recognize that the value is unchanged and that updates to the indexes do not need to occur?Also if both fields remain unchanged, does SQL Server know that it doesn't need to modify the data on the disk?My third question involves a schema bound indexed view. If there is an indexed view that is related to the table, with a clustered index on a field that isn't part of the normal indexes on the table and an update statement is issued, but the values did not change, is SQL Server smart enough to know to not update the statistics and the indexes?If the answer to these questions is no, then I think it would be wise to make your business objects or application smart enough to not update fields that haven't changed when you are building an application. If SQL Server is smart enough to recognize when the values of fields haven't changed, then you could write a generic update statement for your business objects or forms that would always update all of the fields and SQL Server would be smart enough to handle the situation and the only penalty is the extra chatter to the database. But if SQL Server blindly writes and updates statistics and indexes regardless of whether or not the values of fields linked to indexes and schema bound views, then it would probably make alot of sense to optimize your application to only update table fields that have actually changed. |
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-11-06 : 00:51:29
|
| I did some testing and it seems the logical IO is same (trying to read the data) while update when the data is modified Vs when data is not modified, but the actual execution time increases when the dtaa is modified, leads me to belive that while reads would be same in both scenario, writes only occur when data changes. |
 |
|
|
|
|
|
|
|
SQL Server Behavior when executing Update Statemen