Search code examples
sqlsql-serversql-server-2017

Which way to delete/drop a column from a big table

My table has 119962649 rows and is over 600 GB in size.

I want to delete/drop one column of type nvarchar(MAX).

Which is the best way to do so?

Help me, thank you.

Solution

  • You can use alter table tab drop column col;. But if you have a big table or PK,FK, check constraint, or indexes defined on it, then you need to be careful. So, here are my thoughts -

    1. backup the table.
    select * into tab_bkp from  old_tab;
    1. if that column has constraint, you need to drop it first.
    alter table tab drop constraint cons_1;
    1. Drop column from table.
    alter table tab drop column col;
    1. Check data. Anything wrong, you can retrieve data from tab_bkp.