> ## Documentation Index
> Fetch the complete documentation index at: https://mehen.ophi.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL metrics

> What the mehen-sql analyzer measures: query-block structure, CTE graphs, join graphs, predicate complexity, object-touch risk, and an SQL Halstead.

A dedicated SQL analyzer (`mehen-sql`) introduces a new `sql.*` metric namespace tailored to
standalone `.sql` files — ad-hoc queries, analytics models, migration scripts, stored-program
bodies, DDL packages, and mixed scripts. It is backed by the
[sqruff](https://github.com/quarylabs/sqruff) dialect-aware SQL parser.

<Note>
  SQL files (`.sql`, `.ddl`, `.dml`) are analyzed automatically. The dialect is inferred from syntax
  hints with a reported confidence (`sql.dialect.confidence`), falling back to ANSI. mehen compiles
  ANSI, postgres, T-SQL, snowflake, bigquery, mysql, sqlite, oracle, clickhouse, redshift, sparksql,
  athena, and db2.
</Note>

## Selecting a dialect

Dialect inference is a best-effort guess. To pin a file's dialect deterministically, add an in-file
directive on its own line, using the same syntax as
[SQLFluff's in-file configuration](https://docs.sqlfluff.com/en/stable/configuration/setting_configuration.html#in-file-configuration-directives):

```sql theme={null}
-- sqlfluff:dialect:postgres
SELECT id FROM users RETURNING id;
```

When present, the directive overrides inference: `sql.dialect.confidence` is reported as `1.0`,
`sql.dialect.directive_present` is `1`, and `sql.dialect.is_<dialect>` reflects the pinned dialect.

mehen mirrors SQLFluff's parsing of this directive:

* Both `-- sqlfluff:dialect:<name>` and `--sqlfluff:dialect:<name>` (no space) are accepted.
  Whitespace around the `dialect:<name>` separator is ignored, but the `sqlfluff:` prefix itself must
  be exact.
* The directive must start at the beginning of the line — an **indented** directive is ignored (this
  matches SQLFluff).
* Only `--` line comments are honored; **block comments** (`/* sqlfluff:dialect:… */`) are not.
* If several directives appear, the **last** one wins.
* An **unknown** dialect name (`sql.dialect.unknown`) or one **not compiled** into this build such as
  `databricks`, `duckdb`, or `trino` (`sql.dialect.unsupported`) emits a non-blocking warning and
  falls back to inference — it never aborts the analysis.

<Note>
  sqruff (mehen's parser) does **not** itself consume SQLFluff in-file configuration — it silently
  ignores the directive (and older builds panic on some inline-config forms). mehen therefore parses
  the `dialect` directive itself and only ever hands sqruff a resolved, validated dialect. One
  intentional divergence from SQLFluff: SQLFluff matches dialect names **case-sensitively**, and so
  does mehen — `-- sqlfluff:dialect:Postgres` (capital P) is reported as an unknown dialect, exactly as
  SQLFluff would reject it.
</Note>

## Why SQL gets its own family

SQL should not be squeezed into the existing function/class-centric metric model. The dominant
complexity mechanism in standalone SQL is **relational/dataflow structure** rather than imperative
control flow:

* Cyclomatic complexity is meaningful for procedural PL/SQL or T-SQL, but not for ordinary declarative
  SELECT-heavy files.
* A SELECT with 10 joins and 5 CTEs may have no imperative branches while still being difficult to
  review.
* Object-touch risk (DROP, TRUNCATE, MERGE without WHERE) often dominates "review burden" in migration
  scripts.

## Metric namespaces

These `sql.*` keys are published today (raw metrics — research foundation §15):

| Namespace                                                                  | Keys                                                                                                                                      |
| -------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `sql.loc.*`                                                                | `physical`, `code`, `comment`, `blank`, `logical`, `comment_density`, `max_statement_lines`, `avg_statement_lines`.                       |
| `sql.statement.*`                                                          | `count`, `kind_count.<kind>`, `kind_distinct`, `kind_entropy`, `unparsed_count`.                                                          |
| `sql.query_block.*`                                                        | `count`, `max_depth`, `avg_select_items`, `max_select_items`.                                                                             |
| `sql.cte.*`                                                                | `count`, `recursive_count`, `dependency_edges`, `max_dependency_depth`, `max_fan_out`, `unused_count`.                                    |
| `sql.join.*`                                                               | `count`, `kind_count.<kind>`, `outer_count`, `cross_count`, `natural_count`, `non_equi_count`, `missing_condition_count`.                 |
| `sql.subquery.*`, `sql.derived_table.*`                                    | `count`, `max_depth`, `correlated_count`, `scalar_count`, `exists_count`, `in_count`.                                                     |
| `sql.predicate.*`                                                          | `boolean_operator_count`, `max_boolean_depth`, `not_count`, `comparison_count`, `null_semantics_risk_count`.                              |
| `sql.case.*`                                                               | `count`, `max_depth`, `when_count`, `max_when_count`, `missing_else_count`.                                                               |
| `sql.aggregate.*`, `sql.group_by.*`, `sql.having.*`                        | `function_count`, `distinct_count`, `count`, `rollup_count`, `cube_count`, `grouping_sets_count`.                                         |
| `sql.window.*`                                                             | `function_count`, `frame_count`, `partition_expression_count`, `order_expression_count`.                                                  |
| `sql.set_op.*`                                                             | `count`, `kind_count.<kind>`, `union_all_ratio`.                                                                                          |
| `sql.expression.*`, `sql.function.*`, `sql.cast.*`                         | `max_depth`, `call_count`, `distinct_count`, `nested_call_depth`, `count`.                                                                |
| `sql.select.*`                                                             | `star_count`, `outer_star_count`, `expression_without_alias_count`, `output_alias_coverage`.                                              |
| `sql.identifier.*`, `sql.alias.*`, `sql.relation.*`                        | `unqualified_column_ratio`, `quoted_count`, `table_alias_count`, `ref_count`.                                                             |
| `sql.object.*`, `sql.dml.*`, `sql.ddl.*`, `sql.dcl.*`, `sql.transaction.*` | Object-touch and migration-risk counts (`read_count`, `write_count`, `drop_count`, `truncate_count`, `update_without_where_count`, …).    |
| `sql.dialect.*`                                                            | `confidence`, `conflict_count`, `requested`, `directive_present`, `is_<dialect>`.                                                         |
| `sql.parser.*`                                                             | `diagnostic_count`, `unparsable_segment_count`, `unparsable_line_count`, `unparsable_ratio`.                                              |
| `sql.halstead.*`                                                           | `distinct_operators`, `distinct_operands`, `total_operators`, `total_operands`, `vocabulary`, `length`, `volume`, `difficulty`, `effort`. |

Procedural-SQL metrics (`sql.procedural.*` — cyclomatic/cognitive complexity for PL/SQL and T-SQL
routines) remain on the [roadmap](/metrics/sql/roadmap).

## Composite scores

All six explainable composite scores ship today (research foundation §8):

* `sql.structural_complexity` — CTE depth, join count, subquery depth, CASE depth, window count, set op
  count.
* `sql.cognitive_complexity` — SQL analogue of code cognitive complexity.
* `sql.review_burden_index` — file-level rank (0–100) for likely PR review effort.
* `sql.change_risk_score` — operational risk in migration scripts.
* `sql.maintainability_index` — composite (0–100, higher is better) with band interpretation.
* `sql.modularity_health` — CTE use ratio, fan-out, derived-table penalty (0–100; N/A without CTEs).

## Prior art and scientific basis

The metric model is informed by the following work:

* **SonarQube PL/SQL and T-SQL** — defines cyclomatic complexity for procedural blocks (anonymous
  blocks, procedures, triggers, loops, `WHEN`, `IF`/`ELSIF`, `RAISE`, `AND`/`OR`).
  [PL/SQL docs](https://docs.sonarsource.com/sonarqube-server/analyzing-source-code/languages/pl-sql) ·
  [T-SQL docs](https://docs.sonarsource.com/sonarqube-server/analyzing-source-code/languages/t-sql).
* **SQLFluff** and **sqruff** — dialect-aware parsing and linting; their structure rules
  (nested-CASE, unused-CTE, ambiguous-column-count, qualification, implicit cross-join) are
  reusable inspiration for metric contributors.
  [SQLFluff docs](https://docs.sqlfluff.com/en/stable/) ·
  [sqruff docs](https://playground.quary.dev/docs/).
* **`sqlfluff-complexity` plugin** — practical baseline for CPX-style metrics: CTE count, join
  count, nested subquery depth, CASE expressions, boolean operators, window functions, CTE
  dependency depth, set operations, derived tables.
  [Repo](https://github.com/yu-iskw/sqlfluff-complexity).
* **Vashistha & Jain — *Measuring Query Complexity in SQLShare Workload*** — frames query
  complexity as cognitive load on users authoring SQL, with operators / operands / runtime /
  Halstead-style measures.
  [PDF](https://uwescience.github.io/sqlshare/pdfs/Jain-Vashistha.pdf).
* **Piattini & Martínez — *Measuring for Database Programs Maintainability*** — early SQL
  maintainability measures with empirical validation.
  [DOI](https://doi.org/10.1007/3-540-44469-6_7).
* **Spider** — text-to-SQL benchmark; its hardness criteria (number of components, selections,
  conditions, keywords like `GROUP BY` / nested subqueries / aggregators) align well with static
  query complexity features.
  [arXiv:1809.08887](https://arxiv.org/abs/1809.08887) ·
  [Benchmark site](https://yale-lily.github.io/spider).

See [SQL metrics roadmap](/metrics/sql/roadmap) for the implementation phases.

## See also

* [Code metrics](/metrics/code/overview) — the existing source-code suite.
* [Markdown metrics](/metrics/markdown/overview) — the existing documentation suite.
* [Concepts → Spaces](/concepts/spaces) — how SQL spaces will fit the existing model.