Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: update field with the same value

Show all headers | View raw

On Feb 11, 12:54 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> migurus (migu...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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!

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