Index disable and enable while insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Index disable and enable while insert

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2014-01-30 : 19:04:30

I am running this query
but getting error.
1. Disabling the Clustered Index
2.Do the insert bulk
3.Rebuild the index

TRUNCATE TABLE Temp_MissingOLSMachine

-- CREATE TABLE Temp_MissingOLSMachine(
-- EntitlementID NVARCHAR(128) COLLATE Latin1_General_CS_AS
--)

--CREATE CLUSTERED INDEX CIX_MissingOLSMachine_EntitlementID
-- ON Temp_MissingOLSMachine(EntitlementID)

ALTER INDEX [CIX_MissingOLSMachine_EntitlementID] ON Temp_MissingOLSMachine DISABLE
GO
INSERT INTO Temp_MissingOLSMachine
SELECT EntitlementID
FROM DML.ExceptionsTrackingTable
WHERE ExceptionType = N'No OLS Machine Data'

select * from Temp_MissingOLSMachine

ALTER INDEX [CIX_MissingOLSMachine_EntitlementID] ON Temp_MissingOLSMachine REBUILD
GO

But i am getting an error of

Msg 8655, Level 16, State 1, Line 1
The query processor is unable to produce a plan because the index 'CIX_MissingOLSMachine_EntitlementID' on table or view 'Temp_MissingOLSMachine' is disabled.

Please help.

Thanks,
Gangadhara MS
SQL Developer and DBA

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-01-31 : 08:32:48

Before Index Rebuild you want to enable the Index first

Javeed Ahmed

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2014-01-31 : 12:15:02

But enabling means that i need to do the rebuild only right.

ALTER INDEX [CIX_MissingOLSMachine_EntitlementID] ON Temp_MissingOLSMachine DISABLE
GO
INSERT INTO Temp_MissingOLSMachine
SELECT EntitlementID
FROM DML.ExceptionsTrackingTable
WHERE ExceptionType = N'No OLS Machine Data'

ALTER INDEX [CIX_MissingOLSMachine_EntitlementID] ON Temp_MissingOLSMachine REBUILD

select * from Temp_MissingOLSMachine
GO

Could you please post the snippet of the query to enable the Clustred Index.

Thanks,
Gangadhara

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-02-03 : 07:09:47

try this
ALTER INDEX ALL ON Temp_MissingOLSMachine

Javeed Ahmed

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-02-04 : 14:59:13

You cannot disable the clustered index - since that IS the table. Disabling it will prevent all access to the table - insert/delete/update/select...

What you want to do is disable all NC indexes, perform your bulk insert, then rebuild all indexes (including the clustered index). To rebuild all indexes you issue this statement:

ALTER INDEX ALL ON Temp_MissingOLSMachine REBUILD;

If you drop the clustered index, then you would have to recreate it after the load - and adding a clustered index to a table will force a rebuild of all NC indexes for both the drop and the create.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources