Some time while loading data to tables due to having of indexes, data population makes slow. So while loading data to table we need to disable non clustered indexes and after loading of data we need to rebuild indexes.
Below script will disable existing indexes and rebuild with the parameter of table name
Below script will disable existing indexes and rebuild with the parameter of table name
Disable all indexes in
table
Create proc DISABLEIndex(@schema varchar(50),@Tablename varchar(200))
as
Begin
DECLARE @indexquery NVARCHAR(200);
DECLARE CURSOR_Var CURSOR FOR
SELECT 'ALTER INDEX ' + i.name + ' ON ' + s.name+'.'+t.name + ' DISABLE' FROM
sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id
join sys.schemas s on s.schema_id=t.schema_id
WHERE t.name=@Tablename and s.name=@schema
and i.type_desc = 'NONCLUSTERED'
ORDER BY t.name, i.name;
OPEN CURSOR_Var;
FETCH NEXT FROM CURSOR_Var INTO @indexquery;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @indexquery;
FETCH NEXT FROM CURSOR_Var INTO @indexquery;
END;
CLOSE CURSOR_Var;
DEALLOCATE CURSOR_Var;
End
Rebuild all indexes in
table
Create proc REBUILDIndex(@schema varchar(50),@Tablename varchar(200))
as
Begin
DECLARE @indexquery NVARCHAR(200);
DECLARE CURSOR_Var CURSOR FOR
SELECT 'ALTER INDEX ' + i.name + ' ON ' + s.name+'.'+t.name + ' REBUILD' FROM
sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id
join sys.schemas s on s.schema_id=t.schema_id
WHERE t.name=@Tablename and s.name=@schema
and i.type_desc = 'NONCLUSTERED'
and i.is_disabled = 1
OPEN CURSOR_Var;
FETCH NEXT FROM CURSOR_Var INTO @indexquery;
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE sp_executesql @indexquery;
FETCH NEXT FROM CURSOR_Var INTO @indexquery;
END;
CLOSE CURSOR_Var;
DEALLOCATE CURSOR_Var;
ENd
Execute above script
like below
exec DISABLEIndex 'sampleschema','Tablename'
exec REBUILDIndex 'sampleschema','Tablename'
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
