Fastest way to disable indices and drop constraints and rebuild SQL Server table Below are some T-SQL commands that we're running on a table with a few billion records.  Most of the database's size is taken up by 5 tables like this.  What is the quickest way to perform these steps without causing any issues?  It takes more than an hour just to run the 1st command.  Would it just be easier to drop the entire table and re-create it?  Or is that not possible and safe with so much data?  Can anyone think of any other ideas to speed this up?  We are just trying to truncate the data, and then rebuild the table from scratch in our ETL process.

        DROP INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] WITH ( ONLINE = OFF )
        ALTER TABLE OBS_FACT DROP CONSTRAINT OBS_FACT_PK
        ALTER INDEX ALL ON OBS_FACT disable;

        -- add new data to OBS_FACT table via ETL process

        ALTER TABLE [dbo].[OBS_FACT] ADD CONSTRAINT [OBS_FACT_PK] PRIMARY KEY NONCLUSTERED 
        (
            [ENCOUNTER_NUM] ASC,
            [CONCEPT_CD] ASC,
            [PROVIDER_ID] ASC,
            [START_DATE] ASC,
            [MODIFIER_CD] ASC,
            [INSTANCE_NUM] ASC
        ) ON [PRIMARY]

        CREATE CLUSTERED INDEX [OF_IDX_ClusteredConcept] ON [dbo].[OBS_FACT] 
        (
            [CONCEPT_CD] ASC
        );  

        -- REBUILD indexes on OBSERVATION_FACT
        ALTER INDEX ALL ON OBS_FACT REBUILD


Often times, the drop index command causes this error in SQL Server Management Studio, if you try to re-launch SQL Server Management Studio in a different Window.


  Lock request time out period exceeded (Microsoft SQL Server, Error:
  1222)
If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful. 

If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your Clustered Index order in the first place.

However, if you want to disable your indexes, then that would be as follows:

-- Disable indexes on OBSERVATION_FACT
-- If you're dropping, don't disable. If you're disabling, don't drop...
ALTER INDEX ALL ON OBS_FACT DISABLE;

-- Truncate your table    
TRUNCATE TABLE dbo.OBS_FACT;

-- ETL Process here....

-- REBUILD indexes on OBSERVATION_FACT
-- Or recreate them if you've dropped them in step 1
ALTER INDEX ALL ON OBS_FACT REBUILD WITH (ONLINE = ON);


Creating following by immediately rebuilding your indexes as per your script, is pointless, as creating builds the index. Why then immediately rebuild?