title: Changing a datatype in a table without reaching the transaction log limits
body: <p>I have a huge table with about 100 million rows in SQL Server and want to change the datatype from <code>varchar</code> to <code>nvarchar</code> like this:</p>
  
  <pre><code>ALTER TABLE my_table 
      ALTER COLUMN comment NVARCHAR(250);
  </code></pre>
  
  <p><strong>The problem is that my transaction log grows until the hard disk is full.</strong> </p>
  
  <p>I have thought about kind of a bulk transaction but this is basically only one statement. </p>
  
  <p>Does a temp table work when the new tables are created using the new datatypes?   </p>
  
  <pre><code>CREATE TABLE my_table_new (comment NVARCHAR(250);
  
  INSERT INTO my_table_new 
      SELECT * 
      FROM my_table;
  
  -- or
  
  SELECT comment
  INTO my_table_new
  FROM my_table;
  
  DROP TABLE my_table;
  </code></pre>
  
  <p>What is the best approach to do this? </p>
  
  <p><strong>Any idea how I can avoid the extrem growth of the transaction log file?</strong></p>