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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
dbaSQLAuthor Commented:
Yes, I'm at sp1.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
brejkCommented:
How about using COUNT_BIG :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.