title: How is Unicode (UTF-16) data that is out of collation stored in varchar column?
body: <p>This is purely theoretical question to wrap my head around</p>
  
  <p>Let's say I have <code>Unicode</code> cyclone ( <code>1F300</code>) symbol. If I try to store it in <code>varchar</code> column that has default <code>Latin1_General_CI_AS</code> collation, cyclone symbol cannot not fit into one byte that is used per symbol in <code>varchar</code>... </p>
  
  <p>The ways I can see this done:</p>
  
  <ol>
  <li>Like javascript does for symbols out of Basic plane(<a href="https://en.wikipedia.org/wiki/Plane_(Unicode)" rel="nofollow noreferrer">BMP</a>) where it stores them as 2 symbols (surrogate pairs), and then additional processing is needed to <a href="https://stackoverflow.com/questions/37596239/how-can-i-improve-performance-of-utf32-to-utf16-surrogate-pair-converter/37674792#answer-37674792">put them back together</a>...   </li>
  <li>Just truncate the symbol, store first byte and drop the second.... (data is toast - you should have read the manual....)</li>
  <li>Data is destroyed and nothing of use is saved... (data is toast - you should have read the manual....)</li>
  <li>Some other option that is outside of my mental capacity.....</li>
  </ol>
  
  <p>I have done some research after inserting couple of different unicode symbols </p>
  
  <pre><code> INSERT INTO [Table] (Field1)
   VALUES ('')
  
   INSERT INTO [Table] (Field1)
   VALUES ('')
  </code></pre>
  
  <p>and then reading them as bytes <code>SELECT 
  cast (field1 as varbinary(10))</code>  in both cases I got <code>0x3F3F</code>.</p>
  
  <p><a href="https://i.stack.imgur.com/q9792.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/q9792.png" alt="enter image description here"></a></p>
  
  <p><code>3F</code> in <code>ascii</code> is <code>?</code> (<a href="http://ascii.cl/" rel="nofollow noreferrer">question mark</a>) e.g two question marks (<code>??</code>) that I also see when doing normal <code>select *</code> does that mean that data is toast and not even 1st bite is being stored?</p>
  
  <p><strong>How is Unicode data that is out of collation stored in varchar column?</strong></p>