SQL Server Indexes — Making Queries Fast

Without an index — table scan:
  SELECT * FROM Prescriptions WHERE PatientMrn = 'MRN001'
  SQL Server reads EVERY row in the table (1 million rows for 1 match)
  Cost: O(n) — grows with table size

With a non-clustered index on PatientMrn:
  SQL Server follows the index B-tree to MRN001's entry
  Jumps directly to the matching rows
  Cost: O(log n) — barely grows with table size

Clustered index:
  Defines the physical ORDER of rows on disk (one per table)
  The primary key is the clustered index by default
  Range queries on the clustered key are very fast (rows are contiguous)

Non-clustered index:
  A separate structure that points back to the clustered row
  Can have many per table (but each has a write overhead on INSERT/UPDATE)
  The pointer adds a "key lookup" cost if the index doesn't cover all selected columns

Covering index:
  A non-clustered index that INCLUDEs all columns needed by the query
  No key lookup required — the index alone satisfies the query
  Fastest reads, highest storage cost

-- Prescriptions table: the most queried table in the system
-- Queries: by patient, by status, by prescriber, recent prescriptions by ward

CREATE TABLE Prescriptions (
    Id              UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),
    PatientMrn      NVARCHAR(20)     NOT NULL,
    PrescriberId    UNIQUEIDENTIFIER NOT NULL,
    WardId          UNIQUEIDENTIFIER NULL,
    MedicationName  NVARCHAR(200)    NOT NULL,
    Status          NVARCHAR(20)     NOT NULL,  -- 'Draft', 'Approved', 'Suspended'
    CreatedAt       DATETIME2        NOT NULL DEFAULT SYSUTCDATETIME(),
    ApprovedAt      DATETIME2        NULL,
    InrValue        DECIMAL(4,2)     NULL,
    CONSTRAINT PK_Prescriptions PRIMARY KEY CLUSTERED (Id)
);

-- Most common query: active prescriptions for a patient
-- Without index: full table scan
-- With covering index: index-only scan (no key lookup)
CREATE NONCLUSTERED INDEX IX_Prescriptions_Patient_Status
    ON Prescriptions (PatientMrn, Status)
    INCLUDE (MedicationName, InrValue, ApprovedAt, CreatedAt);
-- Covers: SELECT MedicationName, InrValue, ApprovedAt FROM Prescriptions
--         WHERE PatientMrn = 'MRN001' AND Status = 'Approved'

-- Prescriber dashboard: recent prescriptions by prescriber
CREATE NONCLUSTERED INDEX IX_Prescriptions_Prescriber_Created
    ON Prescriptions (PrescriberId, CreatedAt DESC)
    INCLUDE (PatientMrn, MedicationName, Status);

-- Ward view: all active prescriptions on a ward (for the ward round copilot)
CREATE NONCLUSTERED INDEX IX_Prescriptions_Ward_Status
    ON Prescriptions (WardId, Status)
    INCLUDE (PatientMrn, MedicationName, InrValue);

-- Filtered index: only active prescriptions (Status = 'Approved')
-- Much smaller index — fastest when you only ever query active prescriptions
CREATE NONCLUSTERED INDEX IX_Prescriptions_Active_Ward
    ON Prescriptions (WardId, PatientMrn)
    INCLUDE (MedicationName, InrValue, ApprovedAt)
    WHERE Status = 'Approved';
-- Only maintains index entries for approved rows — much faster to maintain on writes

// EF Core Fluent API for index configuration

public sealed class PrescriptionConfiguration : IEntityTypeConfiguration<Prescription>
{
    public void Configure(EntityTypeBuilder<Prescription> builder)
    {
        builder.ToTable("Prescriptions");
        builder.HasKey(p => p.Id);

        builder.Property(p => p.PatientMrn)
               .HasColumnType("nvarchar(20)")
               .IsRequired();

        builder.Property(p => p.Status)
               .HasConversion<string>()
               .HasMaxLength(20)
               .IsRequired();

        // Covering index for patient prescription lookup
        builder.HasIndex(p => new { p.PatientMrn, p.Status })
               .HasDatabaseName("IX_Prescriptions_Patient_Status")
               .IncludeProperties(p => new { p.MedicationName, p.InrValue, p.ApprovedAt, p.CreatedAt });

        // Filtered index for active prescriptions — use HasFilter
        builder.HasIndex(p => new { p.WardId, p.PatientMrn })
               .HasDatabaseName("IX_Prescriptions_Active_Ward")
               .HasFilter("[Status] = 'Approved'")
               .IncludeProperties(p => new { p.MedicationName, p.InrValue, p.ApprovedAt });
    }
}

-- SQL Server does NOT automatically index foreign keys
-- Every JOIN or DELETE on the parent table will scan the FK column without an index

-- Example: PatientId foreign key in Prescriptions
-- Query: "delete a patient and all their prescriptions"
-- Without FK index: SQL Server scans all prescriptions looking for patient matches
-- With FK index:    direct lookup

-- Always index foreign keys:
CREATE NONCLUSTERED INDEX IX_Prescriptions_PatientId
    ON Prescriptions (PatientId);

CREATE NONCLUSTERED INDEX IX_Prescriptions_WardId
    ON Prescriptions (WardId);

CREATE NONCLUSTERED INDEX IX_Prescriptions_PrescriberId
    ON Prescriptions (PrescriberId);

-- EF Core: HasIndex for FK columns
builder.HasIndex(p => p.PatientId)
       .HasDatabaseName("IX_Prescriptions_PatientId");

-- SQL Server tracks queries that would benefit from an index
-- This DMV shows index recommendations with estimated impact

SELECT TOP 20
    mid.statement                                     AS TableName,
    migs.avg_total_user_cost * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans)         AS IndexAdvantage,
    migs.user_seeks,
    migs.user_scans,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid
    ON mid.index_handle = mig.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY IndexAdvantage DESC;

-- Index usage stats — find indexes never used (overhead with no benefit):
SELECT
    OBJECT_NAME(i.object_id)       AS TableName,
    i.name                         AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON s.object_id = i.object_id AND s.index_id = i.index_id
    AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 0  -- exclude heap
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) ASC;
-- Indexes with 0 seeks/scans since last restart are candidates for removal

-- Fragmentation degrades performance over time (especially GUID primary keys)
-- Check fragmentation:
SELECT
    OBJECT_NAME(ips.object_id)          AS TableName,
    i.name                              AS IndexName,
    ips.avg_fragmentation_in_percent    AS FragPercent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i
    ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Rebuild (offline, full defrag, updates statistics):
ALTER INDEX IX_Prescriptions_Patient_Status ON Prescriptions REBUILD;

-- Reorganize (online, partial defrag):
ALTER INDEX IX_Prescriptions_Patient_Status ON Prescriptions REORGANIZE;

-- Rule of thumb:
--   Fragmentation 10–30%:   REORGANIZE (online, lower impact)
--   Fragmentation above 30%: REBUILD  (higher impact, full defrag)
-- Schedule via SQL Agent: weekly reorganize, monthly rebuild during off-peak