title: How to convert nvarchar to varbinary accurately
body: <p>I have a table which has column <code>[password]</code> stored as <code>nvarchar(max)</code>. I want to convert it into <code>varbinary(max)</code>.</p>
  
  <p>I created a new column called <code>[temp]</code> and declared it as <code>varbinary(max)</code>. Then I updated using <code>CONVERT</code>:</p>
  
  <pre><code>update tempuser set [temp]=CONVERT(varbinary(max), CONVERT(nvarchar(max),[password]))
  </code></pre>
  
  <p>Now in the [temp] column the value is different. For example, one value for <code>[password]</code> started with this:</p>
  
  <pre><code>0x3E6AFF88...
  </code></pre>
  
  <p>The corresponding entry in [temp] starts with:</p>
  
  <pre><code>0x30783345...
  </code></pre>
  
  <p>Also when <code>[password]</code> is <code>NULL</code>, <code>[temp]</code> becomes <code>0x4E554C4C</code>.</p>