How Update works for unique values?

Question

Post reply

How Update works for unique values?

vignesh.ms

Hall of Fame

Points: 3456
More actions
November 25, 2014 at 12:08 am

#301442

Hi There,
Look at the following code,

Create table #test
(
id int primary key,
Name varchar(100)
)
insert into #test values (1,'John')
insert into #test values (2,'Walker')
insert into #test values (3,'Bob')
insert into #test values (4,'Tailor')
insert into #test values (5,'Phlip')
insert into #test values (6,'Kevin')
-- Query 1 :
update #test set name = 'Joney' where id = 1
-- Query 2 :
set rowcount 1
update #test set name = 'Joney' where id = 1
set rowcount 0
1. #test table have primary key & clustered index.
2. Obviously only one row will be available for an id.
3. In query 1, will the sql server look for matching rows even after it found 1 row?
4. Will query 2 really gains some performance?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Try comparing the execution plans for each. If they are the same, you have your answer.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

vignesh.ms (11/25/2014)
3. In query 1, will the sql server look for matching rows even after it found 1 row?

No, the column has a unique constraint

4. Will query 2 really gains some performance?

No, if anything then on the contrary, superfluous noise for the optimizer.

😎

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Normally in query 1 the execution plan will do an index seek on the clustered index. Since it is unique, it will know to stop when it has found a row.

I would avoid query 2 (and specifically SET ROWCOUNT) at all costs. Using SET ROWCOUNT for performance tuning is not the way to go.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Actually, that reminds me.

Here is a quote from BOL:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. For more information, see TOP (Transact-SQL).

So don't use it like that.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 5

Thanks for the info.. its really strong informative..