To sum and count bigint values in sql server we might not able to use the traditional functions (SUM and COUNT) as they are. Let us see the problem and how to solve it.
--create temp table for our test if object_id('tempdb.sys.#table1') is not null drop table #table1; create table #table1( col1 int ); --insert test values insert into #table1 (col1) values(2147483647), (2147483647); --total value of col1 select sum(col1) col1Total from #table1; |
As you can see we get error “Arithmetic overflow error converting expression to data type int.”
--solution is simply cast the column to bigint select sum(cast(col1 as bigint)) from #table1; |
Now let us try to count a table that has the maximum number of records on int data type(2147483647).
--let us prepare the table insert into #table1 (col1) select 1; go 2200000000 --this will execute the batch 2200000000 times |
This time to get the total number of records count() function will no longer work for us.
SELECT COUNT_BIG(*) [Total Records] FROM #table1; |
Read More:
http://technet.microsoft.com/en-us/library/ms190317.aspx