Groups > comp.databases.ms-sqlserver > #1587

Re: update field with the same value

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: update field with the same value
Date	2011-02-11 08:54 +0000
Organization	Erland Sommarskog
Message-ID	<Xns9E8964D22953FYazorman@127.0.0.1> (permalink)
References	<a375a396-34fb-4b90-a4cf-f966939b0161@n16g2000prc.googlegroups.com>

Show all headers | View raw

migurus (migurus@yahoo.com) writes:
> -- 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?

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

update field with the same value migurus <migurus@yahoo.com> - 2011-02-10 16:38 -0800
  Re: update field with the same value Gene Wirchenko <genew@ocis.net> - 2011-02-11 12:05 -0800
  Re: update field with the same value Erland Sommarskog <esquel@sommarskog.se> - 2011-02-11 08:54 +0000
    Re: update field with the same value migurus <migurus@yahoo.com> - 2011-02-11 11:14 -0800
  Re: update field with the same value Gene Wirchenko <genew@ocis.net> - 2011-02-10 17:18 -0800

csiph-web