# Problem Definition

There was an interesting question asked recently in Transact-SQL forum "Basic doubt in Round function".

The problem was stated as following:

`SELECT`

`ROUND(744, -3)`

produced 1000 while

`SELECT`

`ROUND(744.0, -3)`

gave an error "Arithmetic overflow error converting expression to data type numeric."

# Explanation

So, what is happening here? Why we're getting this error? The explanation lies in the way SQL Server determines the type of the constant. In this particular case it figures that it can use precision 4 and scale 1 (1 figure after decimal point). So, that precision will not be enough to hold the value 1000 and thus we're getting the error.

We can verify the type, precision and scale using the following query:

`SELECT`

` `

```
SQL_VARIANT_PROPERTY(744.0,
```

`'BaseType'`

`) `

`as`

```
BaseType,
```

` `

```
SQL_VARIANT_PROPERTY(744.0,
```

`'Precision'`

`) `

`as`

```
Precision
```

`,`

` `

```
SQL_VARIANT_PROPERTY(744.0,
```

`'Scale'`

`) `

`as`

```
Scale,
```

` `

```
SQL_VARIANT_PROPERTY(744.0,
```

`'MaxLength'`

`) `

`as`

```
MaxLength
```

which returns:

BaseType | Precision | Scale | MaxLength |

numeric | 4 | 1 | 5 |

This page in BOL shows what types the constants can be. It does not explain the rules how SQL Server figures it out.

All constants have datatypes. Integer constants are given datatype int, decimal values are given datatype numeric(p,q) where p is the number of digits (not counting leading zeros) in the number, and q is the number of digits to the right of the decimal point (including trailing zeroes).

# Conclusion

As shown in this article it is better to explicitly CAST to the desired type rather than rely on SQL Server making the decision.

# See Also

# Other Languages

This entry participated in the TechNet Guru contributions for June contest and won the Gold prize.

Congratulations on winning the gold! blogs.technet.com/.../technet-guru-awards-june-2013.aspx

Thanks

The two blog posts featuring your article are up!

Wiki Ninjas on TechNet blogs: