title: Why cast/convert from int returns an asterisk
body: <p>Someone recently asked me this question and I thought I'd post it on Stack Overflow to get some input.</p>
  
  <p>Now obviously both of the following scenarios are supposed to fail.</p>
  
  <p>#1:</p>
  
  <pre><code>DECLARE @x BIGINT
  SET @x = 100
  SELECT CAST(@x AS VARCHAR(2))
  </code></pre>
  
  <p>Obvious error:</p>
  
  <blockquote>
    <p>Msg 8115, Level 16, State 2, Line 3<br>
    Arithmetic overflow error converting expression to data type varchar.</p>
  </blockquote>
  
  <p>#2:</p>
  
  <pre><code>DECLARE @x INT
  SET @x = 100
  SELECT CAST(@x AS VARCHAR(2))
  </code></pre>
  
  <p>Not obvious, it returns a * (One would expect this to be an arithmetic overflow as well???)</p>
  
  <hr>
  
  <p>Now my real question is, why??? Is this merely by design or is there history or something
  sinister behind this?</p>
  
  <p>I looked at a few sites and couldn't get a satisfactory answer.</p>
  
  <p>e.g.
  <a href="http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/Why-does-CAST-function-return-an-asterik--star.aspx">http://beyondrelational.com/quiz/sqlserver/tsql/2011/questions/Why-does-CAST-function-return-an-asterik--star.aspx</a></p>
  
  <p><a href="http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx">http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx</a></p>
  
  <p><strong>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??</strong></p>