Many times I have seen that the index is disabled when there is large update operation on the table. Bulk insert of very large file updates in any table using SSIS is usually preceded by disabling the index and followed by enabling the index. I have seen many developers running the following query to disable the index.
USE AdventureWorks
GO
----Diable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact DISABLE
GO
While enabling the same index, I have seen developers using the following INCORRECT syntax, which results in error.
USE AdventureWorks
GO
----INCORRECT Syntax Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact ENABLE
GO
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘ENABLE’.
This is because once the index is disabled, it cannot be enabled, but it must be rebuilt. The following syntax will enable and rebuild the index with optimal performance.
USE AdventureWorks
GO
----Enable Index
ALTER INDEX [IX_StoreContact_ContactTypeID] ON Sales.StoreContact REBUILD
GO
I hope that now you have understood why enabling this syntax in the index throws an error and also how to enable an index with optimal performance.
If you want to read just the syntax, I have written a similar article earlier SQL SERVER – Disable Index – Enable Index – ALTER Index.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Hey! this is good one….really helped me…
Thanks
gr8 post. very helpful indeed.
could you please spare a minute to answer my question below.
I hv sql 2008 installed. Whenever a new db is created, there is a path defaulted for the data and log files.
Is there any global setting available where this path can be changed, which would be reflected whenever a new db is created.
thx in advance.
Denny
Hi Pinal,
Once again a nice post.
@denny,
You can change the default path for the data and log file in the server properties.
Right click on the SQL instance –> Database Settings.
In the Database setting page, under “Database Default Locations’, you can set your custom path for both data and log file.
Hope this will help.
Thanks.
Sudeepta.
Hi Pinal,
Once again a nice post.
@denny,
One step missed in earlier comment, find the correct steps mentioned below.
You can change the default path for the data and log file in the server properties.
Right click on the SQL instance –> Properties –>Database Settings.
In the Database setting page, under “Database Default Locations’, you can set your custom path for both data and log file.
Hope this will help.
Thanks.
Sudeepta.
Hi pinal,
Can u pls tell me how to delete duplicate records.
Thanks,
Naresh
@Naresh
https://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/
Pinal has a good deal of articles here. I found that one by searching for: delete duplicate records
Good one!
Hi,
I just wonder if you have any idea how to make it more flexible e.g. DISABLE / REBUILD all indexes in a table from stored procedure based on sys.tables and specified parameters like @table_name and @status -DISABLE / REBUILD
Thanks ahead,
Hi Vlad,
Go through Pinal’s article
https://blog.sqlauthority.com/2010/05/12/sql-server-understanding-alter-index-all-rebuild-with-disabled-clustered-index/
This would give you a basic knowledge of the commands. Now to put alter index command in a stored procedure, you can use code below:
create proc abc.myproc1
@tabname nvarchar(100), @status varchar(10)
as
begin
declare @sqlbuff nvarchar(255)
set @sqlbuff = ‘Alter index all on abc.’ + @tabname + ‘ ‘ + @status
print @sqlbuff
exec (@sqlbuff)
end
go
exec abc.myproc1 @tabname= test , @status = disable
This would display the command that will be executed by the stored procedure as well.
Cheers, Sepia
Thanks a lot, Sepia. You save me life and carrier :-)
Vlad.
Hello again,
Unfortunately, your idea to ‘ALTER INDEX ALL…’ will not work in my case because disable all including clustered for Primary Key.
I have created more specific store procedure. This one is using only developer created indexes started with ‘IC_’. Also I figure out one issue with it: it has to be deployed in very db for it is working only for one database (you can not specify db name and use it as parameter). I could not fix it, however I left sys.tables inner join for future . In case if you have any idea to make more flexible, please, share this info.
— !!!!!!!! one stored procedure for every database !!!!!!!!!———
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Index__DISABLE_REBUILD]’)
AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[Index__DISABLE_REBUILD]
GO
create proc Index__DISABLE_REBUILD
—- DISABLE/REBUILD indexes in a db for any table based on parameters
— e.g.
— EXEC Index__DISABLE_REBUILD ‘Finance_Detail_Fact’, ‘DISABLE’
— EXEC Index__DISABLE_REBUILD ‘Finance_Detail_Fact’,’REBUILD’
— EXEC Index__DISABLE_REBUILD ‘LOSSES’, ‘DISABLE’
— EXEC Index__DISABLE_REBUILD ‘LOSSES’,’REBUILD’
@table_name nvarchar(100), @status varchar(10)
as
begin
Declare c Cursor For select distinct
‘ALTER INDEX [‘+ i.name +’] on ‘+ t.name +’ ‘+ @status
from sys.tables t inner join
sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id
and ic.column_id = c.column_id
where i.name like ‘IC_%’
and t.name = @table_name
Open c
FETCH NEXT FROM c INTO @Table_name
While @@Fetch_Status=0 Begin
— print @Table_name
exec ( @Table_name)
Fetch next From c into @Table_name
End
Close c
Deallocate c
end
Thanks,
Vlad
Enable All Index of Table:
Example:
ALTER INDEX ALL ON [dbo].[Products]
REBUILD;
GO