SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Index disable and enable while insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 01/30/2014 :  19:04:30  Show Profile  Reply with Quote
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

India
573 Posts

Posted - 01/31/2014 :  08:32:48  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
Before Index Rebuild you want to enable the Index first

Javeed Ahmed
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 01/31/2014 :  12:15:02  Show Profile  Reply with Quote
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
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
573 Posts

Posted - 02/03/2014 :  07:09:47  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
try this
ALTER INDEX ALL ON Temp_MissingOLSMachine

Javeed Ahmed
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
789 Posts

Posted - 02/04/2014 :  14:59:13  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000