problem on @@ROWCOUNT

Question

problem on @@ROWCOUNT

peleg

SSCrazy

Points: 2611
More actions
August 15, 2011 at 6:12 am

#241767

i got this query
UPDATE tts
set tts.Company=t3.IndexId
from TargetTable_Staging tts
inner join (
select t1.id as PKid,t2.id as IndexId
FROM TargetTable t1 inner join Dyn_Company t2 (NOLOCK)
ON t1.Company=t2.Name COLLATE SQL_Latin1_General_CP1_CI_AS )t3 on tts.id=t3.PKid
after the query i try to get the row count :
Select @intErrorCode=@@ERROR,@intRowsCount=@@ROWCOUNT
i get wrong number on @intRowsCount
but when i query it i get the correct result (it takes few seconds between the query executed inside a Stored procedure, and when i manually query the table
any idea?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is there a trigger on the table? Is that SELECT @@rowcount the VERY NEXT statement after the update?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

peleg SSCrazy Points: 2611 More actions · Answer 2

no trigger

and the query immediately after the update

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- run this

SELECT COUNT(*)

from TargetTable_Staging tts

inner join (

select t1.id as PKid,t2.id as IndexId

FROM TargetTable t1 inner join Dyn_Company t2 (NOLOCK)

ON t1.Company=t2.Name COLLATE SQL_Latin1_General_CP1_CI_AS )t3 on tts.id=t3.PKid

-- and this

select COUNT(*)

FROM TargetTable t1 inner join Dyn_Company t2 (NOLOCK)

ON t1.Company=t2.Name COLLATE SQL_Latin1_General_CP1_CI_AS

-- it's likely that the results are different.

-- which one do you want to record?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

peleg SSCrazy Points: 2611 More actions · Answer 4

peleg

SSCrazy

Points: 2611

August 15, 2011 at 8:04 am

#1368620

first one

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

peleg (8/15/2011)
first one

Is your rowcount equal to the result of this query?

SELECT COUNT(*)

FROM TargetTable_Staging tts

WHERE EXISTS (SELECT 1

FROM TargetTable t1

INNER JOIN Dyn_Company t2

ON t1.Company=t2.Name COLLATE SQL_Latin1_General_CP1_CI_AS WHERE t1.id = tts.id)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

peleg SSCrazy Points: 2611 More actions · Answer 6

peleg

SSCrazy

Points: 2611

August 15, 2011 at 8:36 am

#1368646

yes

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

@@ROWCOUNT will be the number of rows updated. Bear in mind that a row may be updated only once. Your findings indicate that for some rows of your target table, the results of the UPDATE ... FROM would touch the same target row more than once.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden