What datatypes can cause "String or binary data would be truncated"?

Question

What kind of datatypes can cause the "String or binary data would be truncated" error? Varchars and other strings are obvious, but what about the "binary data"? Can this include integers or datetimes? Or can this error only be caused by datatypes where you have to specify a length? I could not find any relevant documentation and Google has failed me as well.

Answer 1

I hardly expect Microsoft to reverse-document error messages and try to exhaustively iterate all of the ways in which they could be caused. This is something you will just need to discover through testing.

That said, most types have other validation that occur first, and so would never fail with that specific error message. Most numeric types, for example, have ranges beyond which they overflow, so they return an overflow error instead, and sometimes they're even helpful in identifying the "first" value that caused the problem. Some examples:

DECLARE @x TABLE(a TINYINT);
INSERT @x(a) VALUES(2560); -- 1 char too many

Msg 220, Level 16, State 2, Line 3
Arithmetic overflow error for data type tinyint, value = 2560.

DECLARE @x TABLE(a INT);
INSERT @x(a) VALUES(20000000000); -- 1 char too many

Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.

DECLARE @x TABLE(a DECIMAL(5,2));
INSERT @x(a) VALUES(1000.99); -- 1 char too many

Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.

Datetime has similar validation that will fail on the conversion long before it cares about how many characters you actually passed (even if the part that would remain after truncation is valid):

DECLARE @x TABLE(a DATETIME);
INSERT @x(a) VALUES('2014-05-10 12:34:45612347343483434'); -- many chars too many

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

The binary types do return the same unhelpful error message, e.g.:

DECLARE @x TABLE(a VARBINARY(2));
INSERT @x(a) VALUES(0x111122223333); -- 4 chars too many

You have to be careful though, because sometimes silent truncation happens. This is arguably much worse than getting a pesky error message, because you've actually lost data and it is not recoverable.

DECLARE @x VARBINARY(2) = 0x111122223333, @foo VARCHAR = 'bar';
SELECT @x, @foo;

Result:

0x1111    b

Notice that I didn't even declare a length for VARCHAR - in some cases the default is 1, in others it is 30. So even more reason to always match data type definitions exactly and always be explicit.

Did I not cover a type you're curious about? No problem: You can just try it.