November 25, 2014 at 12:08 am
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
November 25, 2014 at 12:26 am
Try comparing the execution plans for each. If they are the same, you have your answer.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2014 at 12:55 am
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.
😎
November 25, 2014 at 12:56 am
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
November 25, 2014 at 1:27 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2014 at 5:39 am
Thanks for the info.. its really strong informative..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply