Why cast/convert from int returns an asterisk

Question

Someone recently asked me this question and I thought I'd post it on Stack Overflow to get some input.

Now obviously both of the following scenarios are supposed to fail.

#1:

DECLARE @x BIGINT
SET @x = 100
SELECT CAST(@x AS VARCHAR(2))

Obvious error:

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

#2:

DECLARE @x INT
SET @x = 100
SELECT CAST(@x AS VARCHAR(2))

Not obvious, it returns a * (One would expect this to be an arithmetic overflow as well???)

---

Now my real question is, why??? Is this merely by design or is there history or something sinister behind this?

I looked at a few sites and couldn't get a satisfactory answer.

e.g. http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/Why-does-CAST-function-return-an-asterik--star.aspx

http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

Please note I know/understand that when an integer is too large to be converted to a specific sized string that it will be "converted" to an asterisk, this is the obvious answer and I wish I could downvote everyone that keeps on giving this answer. I want to know why an asterisk is used and not an exception thrown, e.g. historical reasons etc??

Answer 1

For even more fun, try this one:

DECLARE @i INT
SET @i = 100
SELECT CAST(@i AS VARCHAR(2)) -- result: '*'
go

DECLARE @i INT
SET @i = 100
SELECT CAST(@i AS NVARCHAR(2)) -- result: Arithmetic overflow error

:)

---

The answer to your query is: "Historical reasons"

The datatypes INT and VARCHAR are older than BIGINT and NVARCHAR. Much older. In fact they're in the original SQL specs. Also older is the exception-suppressing approach of replacing the output with asterisks.

Later on, the SQL folks decided that throwing an error was better/more consistent, etc. than substituting bogus (and usually confusing) output strings. However for consistencies sake they retained the prior behavior for the pre-existing combinations of data-types (so as not to break existing code).

So (much) later when BIGINT and NVARCHAR datatypes were added, they got the new(er) behavior because they were not covered by the grandfathering mentioned above.

Answer 2

You can read on the CAST and CONVERT page on the "Truncating and Rounding Results" section. Int, smallint and tinyint will return * when the result length is too short to display when converted to char or varchar. Other numeric to string conversions will return an error.