37

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??

gotqn
  • 42,737
  • 46
  • 157
  • 243
Helix
  • 449
  • 5
  • 10
  • 2
    Can't say about the difference in behaviour between `int` and `bigint`, but as for choosing the asterisk as the filler character, there might be historical reasons for that. I remember that `*` was used in similar situations in FoxPro, before it in FoxBase, which *might* have been re-enacting the feature/behaviour after dBase, and that would be the earliest I could trace this `*` usage to. – Andriy M Feb 03 '12 at 07:20
  • 1
    Odd. I suspected it might be standards related (with `int` and `cast` being standard, `bigint` not), but according to my reading of the standard, it should always be an error condition (although it should be a truncation error, not an overflow error) – Damien_The_Unbeliever Feb 03 '12 at 07:34
  • 1
    I'll bet that in old times, overflow was handled with the `*`, but in the new day, since bigint was added (bigint wasn't available in SQL Server 7.0) being a datatype they did it right and issue an error. They can't break all code that relies on the `*` so they leave it, until it will be phased out. – KM. Feb 03 '12 at 14:06

2 Answers2

41

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • 2
    This is a great answer. We had a similar problem here, casting 6,7, and 8 digit INT to CHAR(6), resulting in an asterisk for those being 7 or 8. We were expecting to get an error and have our process not import this data. We received an error, but the data was still imported, just replacing this portion with an asterisk: **ErrorMessage: String or binary data would be truncated. ErrorSeverity: 16 ErrorState: 14** – Brien Foss Oct 22 '15 at 15:37
  • These types are in the original SQL specification, but are you saying the weird behaviour with * is also in the specification? – Ed Avis Dec 27 '19 at 11:19
  • 1
    I've filed a feature request: https://feedback.azure.com/forums/908035-sql-server/suggestions/39327520-feature-flag-or-compatibility-level-to-give-error – Ed Avis Dec 27 '19 at 11:26
  • @EdAvisThere may actually be a flag for it. I never researched it and some of the flags are pretty obscure. – RBarryYoung Dec 27 '19 at 15:53
  • That said, they're probably going to reply that there's already a workaround for this, just change your `SELECT CAST(@i AS VARCHAR(2))` to `SELECT CAST(CAST(@i AS NVARCHAR(2)) AS VARCHAR(2))`. – RBarryYoung Dec 27 '19 at 15:57
4

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.

Dinesh
  • 112
  • 8