This is SQL Server 2005, we have an application written in such a way
that all updates are done on all columns of a table subject to change,
even though only one field needs to be modified.

For example:

create table #T (
CID INT,
DT DATETIME,
QTY INT);

INSERT INTO #T
select 1, '20110210 12:10', 10 union all
select 2, '20110210 12:20', 10 union all
select 3, '20110210 12:30', 30 union all
select 4, '20110210 12:40', 40

-- we need to change quantity to 0 on row with CID=2
-- app produces this sql:

update #T
set CID=3,DT='20110210 12:30',QTY=0
where CID=3

My question - how bad is it? in real tables some colums are indexed,
does it make server to try to modify indexes? Is SQL Server smart not
to update columns whose value was not changed?

Your input is much appreciated.
migurus

On Thu, 10 Feb 2011 16:38:22 -0800 (PST), migurus <migurus (AT) yahoo (DOT) com>
wrote:

Based on the comment above, this should be "2".

Why are you worrying about this? Is it running too slow for you?

The internal details are likely beyond your control. Just let
the DBMS do its job.

Sincerely,

Gene Wirchenko

migurus (migurus (AT) yahoo (DOT) com) writes:
If this reflects the actual SQL - save the temp table - it is bad, but for
another reason than you ask about.

The app should produce parameterised SQL and not inline values, but maybe it
does in real life?

As for updating each column, even if unchanged... Since the application is
generating the SQL, it could make the effort to generate an UPDATE statement
with only the columns that changes. Then again, that's an extra complexity
and it could introduce bugs. And if you have stored procedures, you
typically have a procedure that accepts parameters for all columns.

So, all in all, not too much to be worried about - as long as it is
parameterised.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Feb 11, 12:54*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
The app does use parameterised statement, not inline values, I put
that sql in my original post just to illustrate the situation.

To see all pros and cons (complexity, extra effort, etc) I'd like to
undesrtand is SQL Server smart not
to update columns whose value was not changed?

Thanks in advance!

On Fri, 11 Feb 2011 11:14:55 -0800 (PST), migurus <migurus (AT) yahoo (DOT) com>
wrote:

[snip]

It does not matter. You can not do anything about it either way.
And if you did find out, it could easily change in another release.

(You are tilting at windwheels.)

Sincerely,

Gene Wirchenko

migurus (migurus (AT) yahoo (DOT) com) writes:
SQL Server updates the row, and logs the row, so on this level it does not
matter. What may be relevant is indexes. If an unchanged colunm appears in
an index, will SQL Server still update those indexes?

To study this, try this batch in the Northwind database:

BEGIN TRANSACTION

UPDATE Orders
SET CustomerID = 'ALFKI',
EmployeeID = 9
WHERE OrderID = 11000

Then look at the locks (for instance with beta_lockinfo,
http://www.sommarskog.se/sqlutil/beta_lockinfo.html.) You will see that
there are locks on the indexes CustomerOrders and EmployeeOrders.

Now rollback, and change 9 to 2 (which is the current value for order 1100)
and run again and study the locks. You will see that there is no lock on
EmployeeOrders. Thus, SQL Server did realize that it not have to update
the index.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Feb 11, 3:03*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Erland, I greatly appreciate your help here - exact, to the point,
with great illustration.
Thanks a lot.