@@rowcount bug? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

@@rowcount bug?

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 10:07:07

I'm working a proc to build a dimension table in a dimensional schema. I use the same template for all my dimension build procs. In that template, I load up (insert) a staging table from my source data. Then, I capture @@rowcount into a variable for event logging.

Have you ever seen a case where you..
insert into mytable
select columns from several tables joined
.. and @@rowcount captured directly after the dml doesn not match the number of rows actually inserted?

What's worse, if I run the insert stand alone and capture @@rowcount it is correct, but inside this particular proc, @@rowcount if off. Like I said, this only happens on 1 of the 40 or so dimensions I'm building.

SQL2K5, by the way, and no, I haven't tried it on any other box because it is a PITA to move my DW and source data to another box for this test.

Jay White

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 10:10:41

The first tought that strikes me is if you use derived tables and somehow the @@rowcount reflect the number of record in that derived table.
How is @@rowcount compared to total insert? to each of the tables involved in the JOINs?

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 10:31:57

I hate to get too much into the specifics of the query, because I don't want to post enough DDL for you to try to replicate ... my from clause on the select portion of the insert looks like this


    from
        DW.DimFUBAR fubar
        cross join (
            Source.Source1 s
            inner join Source.Source2 ssc
                on s.Source1PK = ssc.Source1FK
            inner join Source.Source3 ss
                on ssc.Source3FK = ss.Source3PK
            inner join Toolbox.GetGlobal('GlobalVarKey') gg
                on ss.Column = gg.Value
            left join Source.Source4 c
                on s.Source4FK = c.Source4PK
            left join Source.Source5 cr
                on s.Source5FK = cr.Source5PK
            left join Source.Source6 sx
                on s.Source6PK = sx.Source6PK
            left join Source.Source7 r
                on s.Source7FK = r.Source7PK
            left join Source.Source8 cz
                on s.Source8FK = cz.Source8PK)
    where
        fubar.FubarID = -1
    group by
        s.Source1PK

select
    @RowsInserted = @@rowcount

I use the cross joing because if I don't find values in my left joins, I want to use the corresponding value in my DimFUBAR for my default row (ID = -1).

If there is a pattern between what is actually inserted and what @@rowcount says, I haven't found it. It isn't double. It isn't the number of rows before the grouping. Usually @@rowcount is more than double ...

Many of my Dim builds have insert statements that look just like this ... with the cross join, the join to the table-valued function, some inners, some outers. I only get the problem with this one.

I know this is probably not an answerable question, but I'm hoping someone else had a situation where @@rowcount wasn't right and they figured out why ...

The reason this bugs me is I have a check process at the end of the load that compares the counts at each step of the ETL and reports mismatches. This one dim sticks out as problematic, when infact, it is working correctly...

Jay White

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 05:56:46

No TRIGGER mucking it up I suppose? (What happens if a Trigger does some Inserts of its own, I've never reviewed that)

Kristen

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-13 : 09:14:44

Yeah, there is no trigger ...

Jay White

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 09:31:35

OK, I'll have another shot at removing the potentially absurd:

There aren't two INSERTs to the same target table, and the @RowsInserted only reflects one of the inserts?

Perhaps stick a

SELECT [BEFORE] = COUNT(*) FROM TargetTable

before the insert, and a:

SELECT [BEFORE] = COUNT(*), [@RowsInserted] = @RowsInserted FROM TargetTable

after it?

Kristen

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-13 : 09:52:15

Before
-----------
0

Warning: Null value is eliminated by an aggregate or other SET operation.
After @RowsInserted
----------- -------------
68269 198292


select count(*) as 'Before' from Stage.DimXXX
    insert into Stage.DimXXX (
        ...    )
    select
        ...
    from
        ...
    where
        ...
    group by
        ...

    --get count of inserted rows
    select
        @RowsInserted = @@rowcount
select count(*) as 'After', @RowsInserted as '@RowsInserted' from Stage.DimXXX

Jay White

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 10:35:30

OK, I believe you!

DBCC CHECKDB is not going to give you heart failure is it?

Would changing it to:


INSERT INTO MyTable
SELECT * FROM
(
    SELECT A, B, C
    from
        DW.DimFUBAR fubar
        cross join (
            Source.Source1 s
            inner join Source.Source2 ssc
                on s.Source1PK = ssc.Source1FK
            inner join Source.Source3 ss
                on ssc.Source3FK = ss.Source3PK
            inner join Toolbox.GetGlobal('GlobalVarKey') gg
                on ss.Column = gg.Value
            left join Source.Source4 c
                on s.Source4FK = c.Source4PK
            left join Source.Source5 cr
                on s.Source5FK = cr.Source5PK
            left join Source.Source6 sx
                on s.Source6PK = sx.Source6PK
            left join Source.Source7 r
                on s.Source7FK = r.Source7PK
            left join Source.Source8 cz
                on s.Source8FK = cz.Source8PK)
    where
        fubar.FubarID = -1
    group by
        s.Source1PK
)

make is any better?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 11:00:39

Is there an index in the table with "Ignore duplicate key" set?

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-13 : 11:28:14

Any partitioning involved?

rockmoose

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 11:31:49

On mature reflection its bound to be the full Moooooooon!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-13 : 11:38:13

Sunspots?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 11:52:58

A butterfly flapped it's wings?

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-13 : 13:12:02

Wrapping select * from (select ....) does not fix the problem.
There is no "Ignore Duplicate Key" index.
CheckDB finds no errors.
There is no partitioning involved.

<shrug>

Jay White

Kristen
Test

22859 Posts

Posted - 2006-10-13 : 14:25:17

Is it possible SELECT COUNT(*) is giving the WRONG answer because of some duff stats or somesuch and @RowsInserted is actually correct?

Maybe needs

DBCC UPDATEUSAGE

or some other form of UPDATE STATS ?

Kristen

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-13 : 15:40:42

Nope, duff stats aint the problem, unless it is also a problem on my actual Dim table. I go from Source->Stage->Final Dim ... all three counts match. Plus, I've probably dropped and recreated this particular Dim Stage table 10 times through dev and debugging this issue. I've had the @@rowcount / count(*) problem since day 1 ...

Jay White

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-13 : 15:45:26

what if, in the sproc you do SELECT * INTO #temp FROM...

The catch the rrowcount

is that correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-13 : 15:55:27

OK, I see your thinking ... narrow the problem down between the destination and source of the insert statement ... Select * into #temp from .... select @@rowcount ... still wrong ... @@rowcount doesn't match the count(*) form #temp

Jay White

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-13 : 15:58:48

The udf?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-13 : 16:04:29

The UDF I can give you ...


create table Toolbox.GLOBAL (
    Name varchar(100) not null,
    Value sql_variant not null,
    constraint pk_GLOBAL primary key clustered (Name, Value)
)
go
create function Toolbox.GetGlobal
(
    @Name varchar(100)
)
returns table
as
    return ( select
                Value
             from
                Toolbox.GLOBAL
             where
                Name = @Name)
go

Jay White

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-13 : 16:04:35

quote:
Originally posted by Page47

OK, I see your thinking ... narrow the problem down between the destination and source of the insert statement ... Select * into #temp from .... select @@rowcount ... still wrong ... @@rowcount doesn't match the count(*) form #temp

Jay White

WTF

Jay's out-smarted SQL Server

EDIT: Is the count higherr or lower?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources