CODEWITHSUNDEEP
sql-server-2017
Sandeep
Sandeep

Reputation: 399

Add column to a huge table

I have a table that has around 13 billion records. Size of this table is around 800 GB. I want to add a column of type tinyint to the table but it takes a lot of time to run add column command. Another option would be to create another table with the additional column and copy data from source table to the new table using BCP (data export and import) or copy data directly to the new table.

Is there a better way to achieve this?

Upvotes: 2

Views: 222

Answers (1)

GoldenDBA
GoldenDBA

Reputation: 46

My preference for tables of this size is to create a new table and then batch the records into it (BCP, Bulk Insert, SSIS, whatever you like). This may take longer but it keeps your log from blowing out. You can also do the most relevant data (say last 30 days) first, swap out the table, then batch in the remaining history so that you can take advantage of the new row immediately...if your application lines up with that strategy.

Upvotes: 1

Related Questions