Menu
I am about to run a massive data insert into my DB. I have managed to work out how to enable and rebuild non-clustered indexes on my tables but I also want to disable/enable primary keys as I believe this will speed up the insertion process.
NOTE: This is over numerous tables and so I assume I need some loop to get the primary key information and run the following to drop it but I'm not sure about recreating it:
ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
450
The syntax to disable a primary key using the ALTER INDEX statement in MS SQL Server is: ALTER INDEX constraint_name ON table_name DISABLE; Let's look at an example of how to disable a primary using the ALTER INDEX statement in SQL Server: ALTER INDEX idx_tblt_emp_id_pk ON [DataAnalytics].
Disable Primary KeyYou can disable a primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).
You can delete (drop) a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. When the primary key is deleted, the corresponding index is deleted.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them. Changing the Recovery model of database to be BULK_LOGGED during the load operation. If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
IIRC clustered indexes cannot be disabled as they govern where the actual data is stored in the pages.
I'm pretty sure you would have to drop the key and re-create it after your insert. Depending on the size of the tables, indexes and insert this may not save you any time.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
