How can I prevent integers being silently turned to asterisks in SQL Server?

Question

Suppose we have a table account, there is one field acct_type varchar(2)

Insert into account(acct_type) values(888)

Output:

+-----------+
| acct_type |
+-----------+
| *         |
+-----------+

I'm expecting it will throw an error when insert statement trigger. Why it's storing * value in table?

Answer 1

For varchar datatype a truncated int will be cast as * instead of throwing an error (in this case as the three digits don't fit in a varchar(2)).

This does not happen with nvarchar

There is no way of changing this behaviour, it is preserved for backwards compatibility. If this is a real problem for you you can add a check constraint that the value in the column is not * but I can't imagine any situation where this is really worth doing.

The solution is just not to do that. If you must insert an INT then validate it is in the range -9 to 99 first. Or always use quotes around values destined for a string column rather than relying on implicit conversions.