SELECT COUNT(*) issues msg 8115

I am in the process of migrating from v2000 to v2008.  I copied all the tables over the weekend from the v2000 production source dataset, to the v2008 dataset-to-be.  some of them are pretty bloody big.  all data copies succeeded fine.  Just now I was attempting a simple row count:

select count(*) from tablename

it ran for :43:56, and failed, issuing this:
Msg 8115, Level 16, State 2, Line 2
Arithmetic overlfow error converting expression to data type int.

table def is simple:  
  inserttime   datetime2
  eventtime datetime2
  mtype char(1)
  mindex int
  orderid char(16)
  reqid char(100)
  gid int

interestingly enough, from v2000 enterprise mgr, just dbl clicking a couple of the bigger tables (in the production source dataset) errors out w/the same message.  hit ok on the error, it goes away and returns me the table properties.

in the v2000 production dataset i am pretty worried that i've reached a max on bigint.  why else would i gen that error?  but, checkident does not indicate that to be the problem.

even if that were the case, i don't see the relation to the v2008 select count(*) i just ran.  i know the table is sitting upwards of 1.7B records.  the mindex never exceeds 4 and the gid is static data, the max gid is 3158.  there is no other int/bigint in the table def.

why would a simple select count(*) issue the arithmetic overflow?
LVL 18
dbaSQLAsked:
Who is Participating?
 
brejkCommented:
How about using COUNT_BIG :-)
0
 
St3veMaxCommented:
Try SELECT COUNT(1) FROM x...
0
 
St3veMaxCommented:
Sorry; Didnt read correctly...It should not cause that, are you fully patched with 2008 ?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dbaSQLAuthor Commented:
Yes, I'm at sp1.
0
 
Patrick MatthewsCommented:
dbaSQL said:
>>i know the table is sitting upwards of 1.7B records

How "upward" is "upwards"?  :)

int maxes out at ~2.1 billion, so if your estimate of the number of records in the table is off, then that could
well be the problem, as COUNT returns an int...
0
 
Patrick MatthewsCommented:
brejk said:
>>How about using COUNT_BIG :-)

That ought to do it--never knew that one existed :)
0
 
dbaSQLAuthor Commented:
aaah.... that must be it.  (and that also explains the similar oddities i'm seeing in the v2000 layer)
i'm running COUNT_BIG now
back soon w/status
0
 
St3veMaxCommented:
never knew that one existed either! Thanks for that!
0
 
dbaSQLAuthor Commented:
count hasn't come back yet, but it also hasn't failed yet.  i just checked my SSIS log.  the table in question is 3,455,768,875.  

..... a little more 'upwards' than i had thought.
0
 
dbaSQLAuthor Commented:
yep.  count_big comes back with the 3455768875
that was it.

i guess we won't be doing any counts in the v2000 layer... :-)

matthews and brejk, i'll split between you -- count_big was the solution, but mr. patrick was correct in that my upwards was too upward
thank you each for the input

0
 
Patrick MatthewsCommented:
dbaSQL,

Glad to help, and it was nice to discover another aggregate function today :)

Regards,

Patrick
0
 
brejkCommented:
dbaSQL,

Glad to help. BTW, COUNT_BIG exists in SQL Server since 7.0 AFAIR (since the bigint data type exists). But not many of us has that big tables as you have ;-)

Cheers,
brejk
0
 
dbaSQLAuthor Commented:
yep, it sure does.  i didn't know that.
thank you, brejk
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.