Friday, June 15, 2007

How to get rid of 'String or binary data would be truncated', divisions by zero or arithmetic overflows in T-SQL

In MS Sql when you try to insert or update a field value or parameter that is bigger than the defined size you get String or binary data would be truncated and the query fails. In order to get rid of this, you can use SET ANSI_WARNINGS OFF. But, warning (not ANSI :) ), there are some drawbacks that you have to be aware of before using SET ANSI_WARNINGS OFF :
  • putting SET ANSI_WARNINGS OFF in a stored procedure will force a procedure recompilation each time, so it is better to set it before the execution of the stored procedure
  • this setting will disable the indexes, so queries might run slower
  • no error will be generated when a division by zero appears and a null value is returned (this might not sound as a drawback, but think that you don't always want to return null in some cases and you want at least to be aware of them)

- disables the warning message from aggregated functions when finding null values
- returns null on divisions by zero and arithmetic overflows
- values are truncated when the length of the data is bigger than the defined size

If you want only part of the functionality above, also check SET ARITHABORT or SET ARITHIGNORE.

Also here is a list of how these options affect each other.