title: Change Datatype of Column in SQL Server
body: <p>We have a SQL Server 2008 database with a table containing more than 1.4 billion records. Due to adjustments of the coordinate system, we have to expand the datatype of the coordinate column from <code>decimal(18, 2)</code> to <code>decimal(18, 3)</code>.</p>
  
  <p>We've tried multiple things but everything resulted in an exception (transactionlog is full) after about 14 hours of execution.</p>
  
  <p>These are the things we tried:</p>
  
  <ol>
  <li><p><strong>Alter Table</strong></p>
  
  <pre><code>ALTER TABLE Adress
  ALTER COLUMN Coordinate decimal(18, 3) NULL
  </code></pre></li>
  <li><p><strong>Designer</strong></p>
  
  <ul>
  <li>Uncheck <code>Tools &gt; Options &gt; Designer &gt; Prevent saving changes that require table re-creation</code></li>
  <li>Open Designer</li>
  <li>Change datatype of column to <code>decimal(18, 3)</code></li>
  <li><code>Right-click &gt; Generate Change Script...</code></li>
  </ul></li>
  </ol>
  
  <p>What this script does, is creating a new table with the new datatype, copying the old data to the new table, drop the old table and rename the new table.</p>
  
  <p>Unfortunately both attempts result in a transaction log full exception after 14 hours of execution.</p>
  
  <p>I thought, that changing the datatype via <code>ALTER TABLE... ALTER COLUMN...</code> is only changing the metadata and should be finished in the matter of (milli)seconds?</p>
  
  <ul>
  <li>Do you know of any other method I could try?</li>
  <li>Why are my attempts (especially #1) needing that much time?</li>
  </ul>
  
  <p>Thanks in advance</p>