In Which order summation is done in SQL Server 2008
select sum(Quantity) from tblXYZ where Condition1 and Condition2 and Live=1 Here Quantity column is integer When i am running above query is runs fine sometime and sometime it fails. Error: "Arithmetic overflow error converting expression to data type int." I know it fails due to intermediate result is greater than INT32.MAX value. If i change it to BIGINT it will solve issue. But i don't want to change it to BIGINT. Here tblXYZ has Date column which is date time, which tells, when all transactions are inserted If i do summation in that order then i think i can resolve the issue. Any help on this is appriciated.
The order of row processing is not guaranteed and you canot override it by any documented way. In general it depends on the query pland and degree of parallelism. If you do not want to change the underlying data type, you can cast it in your query. select sum(CAST(Quantity AS bigint)) from tblXYZ where Condition1 and Condition2 and Live=1