DRAG DROP
You administer a Windows Azure SQL Database database used for data warehouse operations.
The database contains a table named OrdersHistory, defined as follows:
A weekly ETL (extract-transform-load) runs a large INSERT statement to add data into the
OrdersHistory table. The process is taking a long time to complete. You discover that the bulk of the
process is performing non-clustered index updates on the OrdersHistory table.
You need to improve the performance of the ETL process faster. You need to meet the following
requirements:
Avoid losing existing permissions on existing objects.
Use minimal administrative effort.
What should you do? (To answer, move the appropriate actions from the list of actions to the
answer area and arrange them in the correct order.)
Explanation:
Based on this https://technet.microsoft.com/en-us/library/ms177445(v=sql.105).aspx#Anchor_1 the answer is:
1. Run DROP INDEX statement for each index.
2. Run the INSERT statement.
3. Run CREATE INDEX statement for each index.
ALTER INDEX statements are usually faster:
https://sqlstudies.com/2014/12/11/drop-index-and-create-index-vs-alter-index-rebuild/
disable an index causes disabling access to underling table.
drop insert create is the correct answer
Disabled access to the underlying table only occurs if the disabled index is clustered.
Disabling nonclustered Indexes may result in slower data access but will not prevent access.
Because of that you can disable the index and rebuild after the insert has completed.
But rebuild index will cause the table not accessible unless using rebuild ONLINE option