Filtered Indexes & Computed Columns

Status: Reviewed (deep-dive v1)
Stack note: Deepens SQL-16 for boards that always filter Status / RAG — filtered indexes and persisted computed columns keep GetOpenBoard / heat maps thin.
Goal: When filtered IX and computed cols pay off; pitfalls with params and determinism.

Quick chooser

Need	Prefer
Board only sees Open/Acked	Filtered IX `WHERE Status IN (...)`
Heat derived from dates	Persisted computed col + IX
Query uses `@Status` variable	Filtered IX may be skipped — match literals or `RECOMPILE`
Wide INCLUDE lists	Keep lean; don’t duplicate heap

Filtered index

CREATE INDEX IX_Alert_Open_FirstSeen
  ON dbo.Alert (FirstSeenAt, AlertId)
  INCLUDE (Title, SeverityCode, SqlInstanceId, AlertType)
  WHERE Status IN (N'Open', N'Acked');

Pair with pagination (SQL-25) keyset order.

Param tip: WHERE Status = N'Open' matches better than WHERE Status = @Status unless sniffed/recompile cooperates — test plans.

Computed column

ALTER TABLE dbo.SqlDatabaseBackupStatus
ADD BackupAgeHours AS (DATEDIFF(HOUR, LastBackupAt, SYSUTCDATETIME())) PERSISTED;
-- Note: SYSUTCDATETIME in computed is non-deterministic — won't persist.
-- Prefer persisted from stored LastBackupAt vs a stored "as of" or index on LastBackupAt only.

Determinism: persisted computed cols must be deterministic — index LastBackupAt and compute age in the SELECT when “now” is involved.

-- good: persisted classification from stored RAG code
ALTER TABLE dbo.SqlDatabaseBackupStatus
ADD IsCritical AS (CASE WHEN RagStatus = N'R' THEN 1 ELSE 0 END) PERSISTED;

CREATE INDEX IX_Backup_Critical ON dbo.SqlDatabaseBackupStatus (SqlInstanceId)
  WHERE RagStatus = N'R';

Habits

[ ] Filtered IX list documented per board
[ ] Writes (Ack) still cheap enough — don’t over-filter-index
[ ] Stats on filtered IX exist — don’t disable auto-stats
[ ] After Status domain change, rebuild filtered IX definitions

Done when

[ ] Open-alert and critical-backup paths use filtered IX where measured
[ ] No non-deterministic “persisted” age columns
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-29 Filtered Indexes & Computed Columns v1