title: SQL Server fastest way to change data types on large tables
body: <p>We need to change the data type of about 10 primary keys in our db from <code>numeric(19,0)</code> to bigint.  On the smaller tables a simple update of the datatype works just fine but on the larger tables (60-70 million rows) it takes a considerable amount of time.  </p>
  
  <p>What is the fastest way to achieve this, preferably without locking the database.</p>
  
  <p>I've written a script that generates the following (which I believe I got from a different SO post)</p>
  
  <pre><code>--Add a new temporary column to store the changed value.
  ALTER TABLE query_log ADD id_bigint bigint NULL;
  GO
  
  CREATE NONCLUSTERED INDEX IX_query_log_id_bigint ON query_log (id_bigint)
  INCLUDE (id); -- the include only works on SQL 2008 and up
  -- This index may help or hurt performance, I'm not sure... :)
  
  GO
  
  declare @count int
  declare @iteration int
  declare @progress int
  set @iteration = 0
  set @progress = 0
  
  select @count = COUNT(*) from query_log
  RAISERROR ('Processing %d records', 0, 1, @count) WITH NOWAIT
  
  -- Update the table in batches of 10000 at a time
  WHILE 1 = 1 BEGIN 
  
     UPDATE X -- Updating a derived table only works on SQL 2005 and up
     SET X.id_bigint = id
     FROM (
        SELECT TOP 10000 * FROM query_log WHERE id_bigint IS NULL
     ) X;
     IF @@RowCount = 0 BREAK;
     set @iteration = @iteration + 1
     set @progress = @iteration * 10000
     RAISERROR ('processed %d of %d records', 0, 1, @progress, @count) WITH NOWAIT
  END;
  GO
  
  --kill the pkey on the old column
  ALTER TABLE query_log
  DROP CONSTRAINT PK__query_log__53833672
  GO
  
  BEGIN TRAN; -- now do as *little* work as possible in this blocking transaction
  UPDATE T -- catch any updates that happened after we touched the row
  SET T.id_bigint = T.id
  FROM query_log T WITH (TABLOCKX, HOLDLOCK)
  WHERE T.id_bigint &lt;&gt; T.id;
  -- The lock hints ensure everyone is blocked until we do the switcheroo
  
  EXEC sp_rename 'query_log.id', 'id_numeric';
  EXEC sp_rename 'query_log.id_bigint', 'id';
  COMMIT TRAN;
  GO
  
  DROP INDEX IX_query_log_id_bigint ON query_log;    
  GO
  
  ALTER TABLE query_log ALTER COLUMN id bigint NOT NULL;
  GO
  
  /*
  ALTER TABLE query_log DROP COLUMN id_numeric;
  GO
  */
  
  ALTER TABLE query_log
  ADD CONSTRAINT PK_query_log PRIMARY KEY (id)
  GO
  </code></pre>
  
  <p>This works very well for the smaller tables but is extremely slow going for the very large tables.  </p>
  
  <p>Note this is in preparation for a migration to Postgres and the EnterpriseDB Migration toolkit doesn't seem to understand the numeric(19,0) datatype </p>