Database maintenance SQL

SQL Server index Creation by using table

Database performance improve karne ke liye indexes bahut important hote hain. Lekin large ERP ya payroll systems me manually indexes manage karna difficult h...

May 12, 2026 7 min read

Database performance improve karne ke liye indexes bahut important hote hain. Lekin large ERP ya payroll systems me manually indexes manage karna difficult ho jata hai. Isi problem ko solve karne ke liye ek Metadata-Driven Index Management System create kiya gaya hai.

Is approach me saare indexes ek central configuration table me maintain hote hain. Fir stored procedure automatically CREATE, DROP ya REBUILD scripts generate karti hai. Isse DBA aur developers dono ka kaafi time save hota hai.

Main Purpose

Centralized index management
Dynamic index script generation
Easy deployment on multiple servers
Performance tuning standardize karna
Manual mistakes reduce karna

Kaise Kaam Karta Hai

System me ek metadata table maintain hoti hai:

Table Name
Index Name
Key Columns
Include Columns
Filter Conditions
Fill Factor
Active Status

Ye sab details store hoti hain aur stored procedure automatically SQL generate karti hai.

Features

Dynamic CREATE INDEX generation
Existing indexes ka automatic DROP
INCLUDE columns support
Filtered indexes support
FillFactor customization
Active/Inactive index control
Table-wise filtering
Reusable deployment scripts

Benefits

Better Performance

Large transactional tables ki query speed improve hoti hai.

Standardization

Har environment me same indexing structure maintain hota hai.

Easy Maintenance

DBA ko manually hundreds of indexes manage nahi karne padte.

Faster Deployment

Client server migration ya fresh setup me ek hi script sufficient hoti hai.

Safer Changes

Indexes metadata table me maintain hone ki wajah se tracking easy hoti hai.

Real-World Usage

Ye approach especially useful hai:

Payroll Systems
Attendance Systems
ERP Applications
HRMS Platforms
High Transaction Databases

Jahan daily millions of records process hote hain.

Summary

Metadata-driven index management ek smart aur scalable approach hai SQL Server performance tuning ke liye. Isme indexes directly tables par hardcode nahi hote, balki centrally manage kiye jaate hain. Isse maintenance easy hota hai, deployment fast hota hai aur database performance consistent rehti hai.

IF NOT EXISTS (
    SELECT 1 FROM sys.tables WHERE name = 'ADTDB_SysIndexes'
)
BEGIN
    CREATE TABLE [dbo].ADTDB_SysIndexes
    (
        [IndexID]           INT             IDENTITY(1,1)   NOT NULL,
        [SchemaName]        NVARCHAR(128)   NOT NULL        DEFAULT 'dbo',
        IndexCount          SmallINT        NOT NULL        DEFAULT 0,
        [TableName]         NVARCHAR(256)   NOT NULL,
        [IndexName]         NVARCHAR(256)   NOT NULL,
        [IndexType]         NVARCHAR(30)    NOT NULL        DEFAULT 'NONCLUSTERED', -- NONCLUSTERED / CLUSTERED
        [IsUnique]          BIT             NOT NULL        DEFAULT 0,
        [KeyColumns]        NVARCHAR(MAX)   NOT NULL,       -- e.g. [Col1] ASC, [Col2] DESC
        [IncludeColumns]    NVARCHAR(MAX)   NULL,           -- e.g. [Col3], [Col4]  → NULL if no INCLUDE
        [FilterCondition]   NVARCHAR(MAX)   NULL,           -- e.g. [Status] = 1    → NULL if no filter
        [FillFactor]        TINYINT         NOT NULL        DEFAULT 90,
        [IsActive]          BIT             NOT NULL        DEFAULT 1,              -- 0 = skip this index
        [Remarks]           NVARCHAR(500)   NULL,
        [CreatedOn]         DATETIME        NOT NULL        DEFAULT GETDATE()

        CONSTRAINT [PK_ADTDB_SysIndexes] PRIMARY KEY CLUSTERED ([IndexID])
    );

    PRINT 'Table [ADTDB_SysIndexes] created.';
END
ELSE
BEGIN
    PRINT 'Table [ADTDB_SysIndexes] already exists.';
END
GO

TRUNCATE TABLE [dbo].[ADTDB_SysIndexes];
GO

SET IDENTITY_INSERT [dbo].[ADTDB_SysIndexes] ON;
GO

INSERT [dbo].[ADTDB_SysIndexes] ([IndexID],[SchemaName],[IndexCount],[TableName],[IndexName],[IndexType],[IsUnique],[KeyColumns],[IncludeColumns],[FilterCondition],[FillFactor],[IsActive],[Remarks],[CreatedOn])
VALUES (1,N'dbo',1,N'TAMAttendanceDetails',N'uidx_TAMAttendanceDetails_MachineID_EnrollNo',N'NONCLUSTERED',0,N'[MachineID] ASC, [EnrollNo] ASC',N'[TAMDateStr], [TAMTimeStr], [TAMDate]',NULL,90,1,N'TAM Attendance - MachineID + EnrollNo lookup | TAMDate added to INCLUDE (merged from uidx_TAMAttendanceDetails_MachineID)',CAST(N'2026-05-06T16:01:20.397' AS DateTime));
GO

IF OBJECT_ID('uspADT_IndexScript','P') IS NOT NULL
BEGIN
    DROP PROC uspADT_IndexScript
END

GO

CREATE PROCEDURE [dbo].uspADT_IndexScript
    @TableFilter    NVARCHAR(256) = NULL,
    @IndexFilter    NVARCHAR(256) = NULL,
    @Mode           INT = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @IndexID            INT,
        @SchemaName         NVARCHAR(128),
        @TableName          NVARCHAR(256),
        @IndexName          NVARCHAR(256),
        @IndexType          NVARCHAR(30),
        @IsUnique           BIT,
        @KeyColumns         NVARCHAR(MAX),
        @IncludeColumns     NVARCHAR(MAX),
        @FilterCondition    NVARCHAR(MAX),
        @FillFactor         TINYINT,
        @Remarks            NVARCHAR(500),
        @TotalIndexes       INT = 0,
        @CurrentIndex       INT = 0;


    IF @Mode IN (0, 1) 
      BEGIN
        DECLARE @ExistingIndexName NVARCHAR(256);
        DECLARE @ExistingSchemaName NVARCHAR(128);
        DECLARE @ExistingTableName NVARCHAR(256);

        DECLARE cur_ExistingIndexes CURSOR LOCAL FAST_FORWARD FOR
            SELECT
            SCHEMA_NAME(t.schema_id) AS SchemaName,
            t.name AS TableName,
            i.name AS IndexName
        FROM sys.indexes i
            INNER JOIN sys.tables t ON i.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
        WHERE t.name IN (
                SELECT DISTINCT [TableName]
            FROM [dbo].[ADTDB_SysIndexes]
            WHERE [IsActive] = 1
                AND ([TableName] = @TableFilter OR @TableFilter IS NULL)
            )
            AND i.type = 2 -- 2 = Non-Clustered Index
            AND i.name IS NOT NULL;

        OPEN cur_ExistingIndexes;

        FETCH NEXT FROM cur_ExistingIndexes INTO 
            @ExistingSchemaName, @ExistingTableName, @ExistingIndexName;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT '-- DROP existing non-clustered index'
            PRINT 'IF EXISTS'
            PRINT '('
            PRINT '    SELECT 1 FROM sys.indexes'
            PRINT '    WHERE  name      = ''' + @ExistingIndexName + ''''
            PRINT '    AND    object_id = OBJECT_ID(''' + @ExistingSchemaName + '.' + @ExistingTableName + ''')'
            PRINT ')'
            PRINT 'BEGIN'
            PRINT '    DROP INDEX [' + @ExistingIndexName + '] ON [' + @ExistingSchemaName + '].[' + @ExistingTableName + '];'
            PRINT 'END'
            PRINT 'GO'
            PRINT ''

            FETCH NEXT FROM cur_ExistingIndexes INTO 
                @ExistingSchemaName, @ExistingTableName, @ExistingIndexName;
        END

        CLOSE cur_ExistingIndexes;
        DEALLOCATE cur_ExistingIndexes;
    END


    SELECT @TotalIndexes = COUNT(1)
    FROM [dbo].[ADTDB_SysIndexes]
    WHERE  [IsActive] = 1
        AND ([TableName] = @TableFilter OR @TableFilter IS NULL)
        AND ([IndexName] = @IndexFilter OR @IndexFilter IS NULL);

    DECLARE cur_Index CURSOR LOCAL FAST_FORWARD FOR
        SELECT
        [IndexID], [SchemaName], [TableName], [IndexName],
        [IndexType], [IsUnique], [KeyColumns], [IncludeColumns],
        [FilterCondition], [FillFactor], [Remarks]
    FROM [dbo].[ADTDB_SysIndexes]
    WHERE  [IsActive] = 1
        AND ([TableName] = @TableFilter OR @TableFilter IS NULL)
        AND ([IndexName] = @IndexFilter OR @IndexFilter IS NULL)
    ORDER BY [TableName], [IndexName];

    OPEN cur_Index;

    FETCH NEXT FROM cur_Index INTO
        @IndexID, @SchemaName, @TableName, @IndexName,
        @IndexType, @IsUnique, @KeyColumns,
        @IncludeColumns, @FilterCondition, @FillFactor, @Remarks;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @CurrentIndex = @CurrentIndex + 1;

        IF @Mode IN (0, 1)
        BEGIN

            PRINT 'IF NOT EXISTS'
            PRINT '('
            PRINT '    SELECT 1 FROM sys.indexes'
            PRINT '    WHERE  name      = ''' + @IndexName + ''''
            PRINT '    AND    object_id = OBJECT_ID(''' + @SchemaName + '.' + @TableName + ''')'
            PRINT ')'
            PRINT 'BEGIN'
            PRINT '    CREATE '
                  + CASE WHEN @IsUnique = 1 THEN 'UNIQUE ' ELSE '' END
                  + @IndexType + ' INDEX [' + @IndexName + ']'
            PRINT '    ON [' + @SchemaName + '].[' + @TableName + ']'
            PRINT '    ('
            PRINT '        ' + @KeyColumns
            PRINT '    )'

            IF @IncludeColumns IS NOT NULL AND LTRIM(RTRIM(@IncludeColumns)) <> ''
            BEGIN
                PRINT '    INCLUDE'
                PRINT '    ('
                PRINT '        ' + @IncludeColumns
                PRINT '    )'
            END

            IF @FilterCondition IS NOT NULL AND LTRIM(RTRIM(@FilterCondition)) <> ''
                PRINT '    WHERE ' + @FilterCondition

            PRINT '    WITH'
            PRINT '    ('
            PRINT '        PAD_INDEX               = OFF,'
            PRINT '        STATISTICS_NORECOMPUTE  = OFF,'
            PRINT '        SORT_IN_TEMPDB          = OFF,'
            PRINT '        IGNORE_DUP_KEY          = OFF,'
            PRINT '        DROP_EXISTING           = OFF,'
            PRINT '        ONLINE                  = OFF,'
            PRINT '        ALLOW_ROW_LOCKS         = ON,'
            PRINT '        ALLOW_PAGE_LOCKS        = ON,'
            PRINT '        FILLFACTOR              = ' + CAST(@FillFactor AS NVARCHAR(3))
            PRINT '    )'
            PRINT '    ON [PRIMARY];'
            PRINT 'END'
            PRINT 'GO'
            PRINT ''
        END

        ELSE IF @Mode = 2
        BEGIN
            -- ── DROP IF EXISTS──────────────────────────
            PRINT 'IF EXISTS'
            PRINT '('
            PRINT '    SELECT 1 FROM sys.indexes'
            PRINT '    WHERE  name      = ''' + @IndexName + ''''
            PRINT '    AND    object_id = OBJECT_ID(''' + @SchemaName + '.' + @TableName + ''')'
            PRINT ')'
            PRINT 'BEGIN'
            PRINT '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '];'
            PRINT 'END'
            PRINT 'GO'
            PRINT ''

            -- ── CREATE──────────────────────
            PRINT 'CREATE '
                  + CASE WHEN @IsUnique = 1 THEN 'UNIQUE ' ELSE '' END
                  + @IndexType + ' INDEX [' + @IndexName + ']'
            PRINT 'ON [' + @SchemaName + '].[' + @TableName + ']'
            PRINT '('
            PRINT '    ' + @KeyColumns
            PRINT ')'

            IF @IncludeColumns IS NOT NULL AND LTRIM(RTRIM(@IncludeColumns)) <> ''
            BEGIN
                PRINT 'INCLUDE'
                PRINT '('
                PRINT '    ' + @IncludeColumns
                PRINT ')'
            END

            IF @FilterCondition IS NOT NULL AND LTRIM(RTRIM(@FilterCondition)) <> ''
                PRINT 'WHERE ' + @FilterCondition

            PRINT 'WITH'
            PRINT '('
            PRINT '    PAD_INDEX               = OFF,'
            PRINT '    STATISTICS_NORECOMPUTE  = OFF,'
            PRINT '    SORT_IN_TEMPDB          = OFF,'
            PRINT '    IGNORE_DUP_KEY          = OFF,'
            PRINT '    DROP_EXISTING           = OFF,'
            PRINT '    ONLINE                  = OFF,'
            PRINT '    ALLOW_ROW_LOCKS         = ON,'
            PRINT '    ALLOW_PAGE_LOCKS        = ON,'
            PRINT '    FILLFACTOR              = ' + CAST(@FillFactor AS NVARCHAR(3))
            PRINT ')'
            PRINT 'ON [PRIMARY];'
            PRINT 'GO'
            PRINT ''
        END

        FETCH NEXT FROM cur_Index INTO
            @IndexID, @SchemaName, @TableName, @IndexName,
            @IndexType, @IsUnique, @KeyColumns,
            @IncludeColumns, @FilterCondition, @FillFactor, @Remarks;
    END

    CLOSE cur_Index;
    DEALLOCATE cur_Index;


END
GO