Hardcoded IDs in SQL views: a shortcut with no expiry date

01 / The shortcut

Hardcoded IDs work beautifully—until reality moves

This kind of predicate is concise, fast to write, and completely legal:

CREATE VIEW dbo.vw_completed_activity
AS
SELECT
    a.activity_id,
    a.person_id,
    a.completed_at
FROM dbo.activities AS a
WHERE a.category_id IN (3, 7, 11);

Nothing in SQL Server knows that 3 means “course,” 7 means “assessment,” or 11 was retired last Tuesday. The database sees integers. The business sees categories. The fragility lives in that translation.

If category 14 is introduced, the view will not fail. It will omit 14 with perfect reliability.

02 / The hidden contract

A database ID is identity, not business meaning

An ID answers “which row?” It should not need to answer “what policy does this row belong to?” When a numeric identifier becomes a business rule, application state has leaked into code without a contract for change.

The risk is highest when IDs are generated operationally, differ between environments, can be re-seeded during migrations, or represent categories that product teams can add and retire. A comment helps the next reader but does not keep the query correct.

03 / The multiplication

Copy and paste turns one assumption into a change programme

Reporting code repeats itself because reports repeat concepts: active, completed, eligible, billable, funded. One view embeds the list, a second copies it, and a third wraps both with a slightly different exception.

In a reporting remediation engagement, IDs appeared eight to twelve times per principal report across four main views and another five to seven dependencies—roughly 70 to 130 maintenance points. Six to eight columns had been wrong, some for more than two years, while every query still ran.

That story is unpacked in The reports had been wrong for two years and nothing was broken. The short version is that duplication does more than increase effort. It makes a complete fix difficult to prove.

04 / The inventory

Search for literals, then investigate meaning

Source control is the best starting point because it shows history and review context. In SQL Server, sys.sql_modules can also expose deployed definitions. Search for IN (...) lists, equality checks against numeric literals, repeated CASE expressions, and comments containing category or status names.

SELECT
    OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
    OBJECT_NAME(m.object_id) AS object_name,
    o.type_desc,
    m.definition
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON o.object_id = m.object_id
WHERE m.definition LIKE '%category_id%'
  AND m.definition LIKE '% IN (%';

This query is a lead generator, not a correctness scanner. Dynamic SQL, formatting, aliases, encrypted modules, and other predicate shapes can evade it. Every match still needs human review. Also inspect application queries, ORM raw SQL, ETL jobs, exports, and BI datasets—the database is rarely the only hiding place.

05 / The source of truth

Replace the list with a governed relationship

A common fix is a mapping table that expresses membership explicitly. Give it meaningful keys, database constraints, ownership, and lifecycle fields appropriate to the business. Then the view joins to the rule rather than carrying its own private copy.

CREATE TABLE dbo.reporting_category_map (
    report_group varchar(50) NOT NULL,
    category_id int NOT NULL,
    effective_from date NOT NULL,
    effective_to date NULL,
    CONSTRAINT pk_reporting_category_map
        PRIMARY KEY (report_group, category_id, effective_from),
    CONSTRAINT fk_reporting_category_map_category
        FOREIGN KEY (category_id)
        REFERENCES dbo.categories (category_id),
    CONSTRAINT chk_reporting_category_map_dates
        CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

CREATE VIEW dbo.vw_completed_activity
AS
SELECT
    a.activity_id,
    a.person_id,
    a.completed_at
FROM dbo.activities AS a
INNER JOIN dbo.reporting_category_map AS m
    ON m.category_id = a.category_id
   AND m.report_group = 'completed_activity'
   AND a.completed_at >= m.effective_from
   AND (m.effective_to IS NULL OR a.completed_at < DATEADD(day, 1, m.effective_to));

This is an example, not a universal schema. Some domains need stable natural codes, some need a many-to-many classification table, and some need temporal history. The goal is the same: one explicit definition that can change without hunting through view text.