What’s the result of this, in SQL Server?
SELECT CAST(12345 AS VARCHAR)
As you expect, it’s “12345”. How about this?
SELECT CAST(1234567890123456789012345678901 AS VARCHAR)
You may be expecting it to return “1234567890123456789012345678901” but it will in fact throw an error:
Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.
It’s all because a length was not given for the VARCHAR to convert the value to. By default, as documented in MSDN, if no length is specified it will default to 30 when using CAST or CONVERT. This results in the error.
This actually tripped me up a few years back now, and I think could easily trip others up. The way to avoid it is to make sure you explicitly specify the length of a VARCHAR (or CHAR) in all places - this is of course good practice, but sometimes it can be too easy to quickly write a CAST or CONVERT statement without giving proper thought to the size.
It’s also worth a note that when declaring a VARCHAR variable without defining the length, it will default to 1. This is a more dangerous scenario as it can go unspotted as an error is not thrown. e.g.
DECLARE @Data VARCHAR
SET @Data = '123'
SELECT @Data
Returns: “1”
Just in case you find yourself trying a quick CAST or CONVERT to a string without specifying the size, bear this in mind!