Database optimization aur maintenance ke time kabhi-kabhi hume sabhi Non-Clustered Indexes temporarily remove karne padte hain — especially jab:
bulk data load karna ho,
migration chal rahi ho,
heavy ETL process ho,
ya performance testing karni ho.
Is blog me hum ek professional SQL Server 2012 compatible script dekhenge jo:
dynamically sab tables ke Non-Clustered Indexes identify karegi,
safely unhe remove karegi,
aur proper logging bhi degi.
What is a Non-Clustered Index?
Non-Clustered Index ek alag structure hota hai jo:
table data ko directly reorder nahi karta,
but fast searching aur lookup me help karta hai.
Example:
Agar Employee table me EmployeeCode par index ho,
to search fast ho jata hai without scanning complete table.
Why Remove Non-Clustered Indexes?
Common Use Cases
1. Bulk Insert Performance
Jab huge data insert hota hai,
har insert ke sath indexes bhi update hote hain.
Ye process slow kar deta hai.
2. ETL Processing
Large ETL jobs me:
insert
update
merge
operations fast karne ke liye indexes temporarily remove kiye jate hain.
3. Migration / Deployment
Kabhi schema changes ke time indexes conflicts create karte hain.
4. Rebuild Strategy
Purane fragmented indexes ko drop karke fresh recreate kiya jata hai.
Important Warning
Ye script production me directly run mat kariye without:
backup,
maintenance window,
testing.
Kyuki indexes remove hone ke baad:
SELECT queries slow ho sakti hain,
reports impact ho sakte hain,
joins expensive ho sakte hain.
Complete SQL Server 2012 Script
-- ============================================================
-- Drop All NON-CLUSTERED Indexes Dynamically
-- SQL Server 2012 Compatible
-- Author : Himanshu
-- Purpose : Loop through sys.tables and drop all NC indexes
-- ============================================================
DECLARE @SchemaName NVARCHAR(128)
DECLARE @TableName NVARCHAR(128)
DECLARE @IndexName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @Counter INT = 0
PRINT '============================================================'
PRINT ' DROP ALL NON-CLUSTERED INDEXES - STARTED'
PRINT ' Server : ' + @@SERVERNAME
PRINT ' Database: ' + DB_NAME()
PRINT ' Time : ' + CONVERT(VARCHAR, GETDATE(), 120)
PRINT '============================================================'
PRINT ''
-- ----------------------------------------------------------------
-- CURSOR: Loop through all NC indexes across all user tables
-- ----------------------------------------------------------------
DECLARE cur_NCI CURSOR FAST_FORWARD FOR
SELECT
s.name AS SchemaName,
t.name AS TableName,
i.name AS IndexName
FROM
sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE
i.type = 2
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND t.is_ms_shipped = 0
ORDER BY
s.name,
t.name,
i.name
OPEN cur_NCI
FETCH NEXT FROM cur_NCI
INTO @SchemaName, @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Counter = @Counter + 1
PRINT REPLICATE('-', 60)
PRINT ' [' + CAST(@Counter AS VARCHAR) + '] Processing...'
PRINT ' Schema : ' + @SchemaName
PRINT ' Table : ' + @TableName
PRINT ' Index : ' + @IndexName
-- Build DROP statement
SET @SQL = N'DROP INDEX ['
+ @IndexName + N'] '
+ N'ON ['
+ @SchemaName + N'].['
+ @TableName + N'];'
PRINT ' SQL : ' + @SQL
BEGIN TRY
EXEC sp_executesql @SQL
PRINT ' Status : SUCCESS - Index Dropped'
END TRY
BEGIN CATCH
PRINT ' Status : FAILED - ' + ERROR_MESSAGE()
END CATCH
PRINT ''
FETCH NEXT FROM cur_NCI
INTO @SchemaName, @TableName, @IndexName
END
-- ----------------------------------------------------------------
-- Cleanup
-- ----------------------------------------------------------------
CLOSE cur_NCI
DEALLOCATE cur_NCI
PRINT '============================================================'
PRINT ' TOTAL NON-CLUSTERED INDEXES DROPPED : ' + CAST(@Counter AS VARCHAR)
PRINT ' Completed At : ' + CONVERT(VARCHAR, GETDATE(), 120)
PRINT '============================================================'
Script Explanation Step-by-Step
1. System Tables Used
sys.indexes
Sab indexes ki information store karta hai.
sys.tables
Database ki user tables.
sys.schemas
Schema names fetch karne ke liye.
Understanding Important Conditions
i.type = 2
Sirf Non-Clustered Indexes select karega.
Type | Meaning |
|---|---|
1 | Clustered Index |
2 | Non-Clustered Index |
i.is_primary_key = 0
Primary Key indexes remove nahi honge.
Safety purpose ke liye important hai.
i.is_unique_constraint = 0
Unique constraints bhi safe rahenge.
t.is_ms_shipped = 0
System tables skip hongi.
Why Dynamic SQL Used?
Kyuki:
har table ka naam different hota hai,
har index ka naam alag hota hai.
Dynamic SQL runtime par DROP statement generate karta hai.
Example:
DROP INDEX [IX_Employee_Name]
ON [dbo].[Employee];
Why TRY...CATCH Important?
Agar:
index lock ho,
permission issue ho,
dependency ho,
to pura script fail nahi hoga.
Error handle ho jayega aur next index continue karega.
Benefits of This Script
Professional Logging
Har step print hota hai:
table name,
schema,
index name,
success/failure.
SQL Server 2012 Compatible
Older SQL versions me bhi perfectly chalega.
Safe Filtering
PK aur Unique Constraints accidentally delete nahi honge.
Performance Consideration
Agar database me:
1000+ indexes hain,
large tables hain,
to script run hone me time lag sakta hai.
Recommended:
off-hours me run karein,
maintenance window use karein.
Best Practice After Dropping Indexes
Indexes remove karne ke baad:
ETL complete karein,
fir indexes recreate karein.
Kyuki permanently indexes remove rakhna generally recommended nahi hota.
Extra Improvement Ideas
Aap future me:
indexes backup table me save kar sakte ho,
recreate scripts auto-generate kar sakte ho,
specific schema filter laga sakte ho,
dry-run mode add kar sakte ho.
Final Thoughts
Ye script SQL Server DBA, Data Engineer, aur ETL developers ke liye kaafi useful hai jab:
high-volume operations,
migrations,
performance tuning,
maintenance activities
perform karni hoti hain.
Proper safeguards ke sath ye script safely large databases me bhi use ki ja sakti hai.