I have a large partitioned table of 16 billion plus records. I am currently updating one of the columns to NULL using the partition number. The process is cumbersome in that it takes a lot of time to complete the operation. One of the partitions of 70 GB took 11 hours to complete the update. So my question is, is it possible to create a column within a partition so that I can use it to do the update in a faster way? Ideally I'd like to create the new column with NULL values, then delete the old column and rename the new one to the same name, rather than update billions of rows i.e. create NewColumn with null values rename CurrentColumn to CurrentColumn_delete rename NewColumn to CurrentColumn Delete CurrentColumn_delete but only on the partition and not whole table
To answer a question of "how do I update the records in batches" with brief comment:
DECLARE @i INT = 1; /*here you say how many records would you like to update at once*/ DECLARE @batchsize = 100000; WHILE EXISTS(SELECT TOP(1) * FROM [table] WHERE col_X IS NOT NULL) BEGIN print @i BEGIN TRAN UPDATE TOP(@batchsize) [table] SET col_X = NULL WHERE col_X IS NOT NULL; COMMIT SET @i = @i+1 END
This is rather to demonstrate the concept than give you the solution tailored to your needs, because:
- no error handling is in the code
- as J.D. said, we'll need to get deeper insight to your data and schema
- the less rows will meet the condition of not being NULL, the slower the update will be (it will be more difficult to find them)
Other thing that could be relevant to you is to check the indexes on the table - in how many indexes is the updated column used? Not only the table but each index has to be updated too and it goes single-threaded - it influences your performance a lot.
Regarding the rename - it is metadata operation only (but for the whole table) and you could use the following snippet:
ALTER TABLE [table] ADD colX_new int; EXEC sp_rename '[table].colX' , 'colX_old'; EXEC sp_rename '[table].colX_new' , 'colX';
I don't think you can add column to individual partition only.