Prev: SSIS Issue with bulk insert
Next: MS_DataCollectorInternalUser
From: ujjwol on 24 Sep 2009 14:54
What is the best way to disable index while we insert data into a huge table
with
clustered index.

how do we enable it after the insert is done

thanks
ujjwol
From: David Hay on 24 Sep 2009 15:51

drop the index, do the insert, then rebuild the index.
From: Russell Fields on 24 Sep 2009 16:30
ALTER INDEX is what you may be looking for.
http://msdn.microsoft.com/en-us/library/ms188388.aspx
This is better than dropping an index in that the metadata of the index
definition is not lost, so it does not need to be supplied in order to
recreate the index.

Examples from that article:
-- Disable
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
--Rebuild
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;

RLF

"ujjwol" <ujjwol(a)discussions.microsoft.com> wrote in message
news:BA66CAFC-8123-41D3-B7F1-7BFB7FA15FE1(a)microsoft.com...
> What is the best way to disable index while we insert data into a huge
> table
> with
> clustered index.
>
> how do we enable it after the insert is done
>
> thanks
> ujjwol

From: John Bell on 24 Sep 2009 16:43

"Russell Fields" <russellfields(a)nomail.com> wrote in message
news:usamXXVPKHA.1280(a)TK2MSFTNGP04.phx.gbl...
> ALTER INDEX is what you may be looking for.
> http://msdn.microsoft.com/en-us/library/ms188388.aspx
> This is better than dropping an index in that the metadata of the index
> definition is not lost, so it does not need to be supplied in order to
> recreate the index.
>
> Examples from that article:
> -- Disable
> ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
> DISABLE ;
> --Rebuild
> ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
> REBUILD ;
>
> RLF
>
> "ujjwol" <ujjwol(a)discussions.microsoft.com> wrote in message
> news:BA66CAFC-8123-41D3-B7F1-7BFB7FA15FE1(a)microsoft.com...
>> What is the best way to disable index while we insert data into a huge
>> table
>> with
>> clustered index.
>>
>> how do we enable it after the insert is done
>>
>> thanks
>> ujjwol
>
You have to be aware that if this is a clustered index disabling the index
will cause problems if you try to insert data into the table. From
http://msdn.microsoft.com/en-us/library/ms188388.aspx
"Disabling a clustered index prevents access to the data, but the data
remains unmaintained in the B-tree until the index is dropped or rebuilt."

e.g.

USE tempdb
GO

CREATE TABLE t1 ( id int not null constraint PK_t1 PRIMARY KEY CLUSTERED,
val varchar(10) not null )
GO


INSERT INTO t1 ( id, val )
VALUES ( 1, 'One' ), ( 2, 'Two' )
GO

ALTER INDEX PK_t1 ON t1 DISABLE
GO

INSERT INTO t1 ( id, val )
VALUES ( 3, 'Three' )
GO

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

John

 | 
Pages: 1
Prev: SSIS Issue with bulk insert
Next: MS_DataCollectorInternalUser