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

Groups > comp.databases.ms-sqlserver > #1578 > unrolled thread

update field with the same value

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1578 — update field with the same value

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

[toc] | [next] | [standalone]

#1580

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

[snip]

>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?

     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

[toc] | [prev] | [next] | [standalone]

#1587

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

[toc] | [prev] | [next] | [standalone]

#1597

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!

[toc] | [prev] | [next] | [standalone]

#1595

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

>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
         ^
     Based on the comment above, this should be "2".

>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?

     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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web