Remembering that it is good to "get back to basics" every now and then

Question

Remembering that it is good to "get back to basics" every now and then

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Sean Lange (8/7/2013)
An INSTEAD OF trigger can definitely result in [highlight="#ffff11"]@@rowcount being different to SELECT count(x) FROM[/highlight] ...
Same result changing this to an instead of trigger.

Yes, you get @@rowcount = 1.

The OP's original situation was that they wanted to know how many rows in the table.

In your test case the table contains zero rows (using the instead of trigger).

...but @@rowcount returns 1.

My point here is that if you need to know how many rows are in the table, you had better count them, not rely on a function that could be telling you something other than the obvious. 😛

drop table RowCountTest;

go

create table RowCountTest

(

id int

)

go

create trigger RowCountTestTrigger on RowCountTest

instead of insert as

select top 1000 *

into #Something

from syscolumns

go

insert RowCountTest

select 1

select @@ROWCOUNT

select count(*)

from RowCountTest

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);