title: Updating billion rows table
body: <p>We are having a table with billion rows. We just want to UPDATE two columns to NULL for all rows. We were trying to do this in batches. But, we are concerned about the performance issues. We are having clustered columnstore index on this table. Does creating composite non-clustered index on these three columns would help ?  or can we re-load the table to new table &amp; swap the tables ? </p>
  
  <p>Any inputs will be very helpful. </p>
  
  <pre><code>DECLARE @notNULLRecordsCount INT; 
  
  SET @notNULLRecordsCount = 1;
  
  WHILE @notNULLRecordsCount &gt; 0 
      BEGIN 
      BEGIN TRANSACTION;
  
      UPDATE  TOP (100000)
      dbo.BillionRowsTable
      SET     Column1 = NULL,
              Column2 = NULL,
              Column3 = NULL     
      WHERE   Column1 IS NOT NULL OR Column2 IS NOT NULL OR Column3 IS NOT NULL;
  
  
  SET @notNULLRecordsCount = @@ROWCOUNT;
  COMMIT TRANSACTION;
  END 
  </code></pre>