Analysis Rule Common Patterns

Introduction

This topic documents the most common reusable patterns used across rule expressions and provides copy/paste-ready examples.

Advanced rule expression common patterns

Advanced analysis rules in SQL Enlight are implemented as full XSLT expressions executed by the analysis engine. Unlike Simple rules (XPath match expressions), Advanced rules give complete control over rule logic, message generation, auto-fix behavior, and interaction with the analysis context.

Advanced rules are typically used when a rule requires complex logic, conditional branching, aggregation, correlation between multiple nodes, execution of SQL queries against the target server or database, or generation of automatic fixes that modify the original SQL text.

An Advanced rule expression usually consists of:

• XSLT control structures ( xsl:for-each , xsl:if , xsl:choose ) to iterate and filter parsed SQL nodes or context data.

• Calls to SQL Enlight helper functions such as sqml:* , ctx:* , str2:* , and regexp:* for syntax inspection, batch handling, string comparison, and regular expression matching.

• Explicit reading and typing of rule parameters via the $parameters collection, enabling configurable behavior (booleans, numeric thresholds, name filters, regular expressions).

• Emission of analysis results through the shared output-message template, including precise source anchoring, severity, and optional auto-fix instructions.

Advanced rules may operate in Batch scope (parsed SQL only), ContextOnly scope (server or database metadata), or a combination of both. ContextOnly rules commonly execute catalog or DMV queries using ctx:executeQueryDirect() , with error handling gated by $is-design-mode .

Use Advanced analysis rule expressions when Simple XPath rules are insufficient, but prefer Simple rules whenever possible to keep rule definitions concise, performant, and easier to maintain.

Producing findings with output-message

Most rules report a finding by calling the shared template output-message . Provide at minimum msg , desc , and usually type (severity). Optionally provide location or “source” metadata and/or an auto-fix.

<xsl:call-template name="output-message">
  <xsl:with-param name="msg"  select="$v-rulename" />
  <xsl:with-param name="desc" select="concat($v-rulename,' : ', $details)" />
  <xsl:with-param name="type" select="$v-ruleseverity" />
  <!-- Optional location anchoring -->
  <xsl:with-param name="near-element" select="$someAstNode" />
  <!-- Optional “source” metadata -->
  <xsl:with-param name="source" select="concat('[', $database-name, ']')" />
  <xsl:with-param name="source-main-object" select="$database" />
</xsl:call-template>

Auto-fix is typically provided via fix-replace (whole replacement text) or fix-target + fix-replace (surgical replacement), plus an optional fix-description .

<xsl:call-template name="output-message">
  <xsl:with-param name="msg"  select="$v-rulename" />
  <xsl:with-param name="desc" select="concat($v-rulename,' : ','Run DBCC CHECKDB')" />
  <xsl:with-param name="type" select="$v-ruleseverity" />
  <xsl:with-param name="fix-replace" select="'DBCC CHECKDB WITH NO_INFOMSGS;'" />
  <xsl:with-param name="fix-description" select="'Add DBCC CHECKDB WITH NO_INFOMSGS statement'" />
</xsl:call-template>

Rule parameters: reading and typing

Parameters are read from $parameters/Param . Common conversions are: boolean flags stored as “yes/no”, numeric parameters via number() , and string parameters via text() .

<xsl:variable name="CaseSensitive"
              select="boolean($parameters/Param[@Name='CaseSensitive'][text()='yes'])" />

<xsl:variable name="ExpirationDays"
              select="number($parameters/Param[@Name='ExpirationDays']/text())" />

<xsl:variable name="FilterJobsWithStatus"
              select="$parameters/Param[@Name='FilterJobsWithStatus']/text()" />

Some rules support a “regexp:” prefix convention in a parameter value to switch from string compare to regular expression matching.

Batch rules: scanning statements and tokens

Batch rules typically iterate $batch-statements , filter by statement type using sqml:is() , then drill into keyword/token nodes (namespaces such as k: , g: , i: , etc.).

<xsl:for-each select="$batch-statements[sqml:is('UpdateSearched WithCteUpdateSearched', @type)]">
  <xsl:for-each select="k:update/k:set/g:commalist/o:assignment/i:*">
    <xsl:variable name="columnNode" select="." />
    <!-- Evaluate condition... -->
    <xsl:call-template name="output-message">
      <xsl:with-param name="msg" select="$v-rulename" />
      <xsl:with-param name="desc" select="concat($v-rulename,' : ','...')" />
      <xsl:with-param name="type" select="$v-ruleseverity" />
      <xsl:with-param name="near-element" select="$columnNode" />
    </xsl:call-template>
  </xsl:for-each>
</xsl:for-each>

To improve performance, rules may cache repeated XPath selections using ctx:getCachedSelect() .

Case-insensitive compares and casing rules

Prefer str2:compare(a,b,caseInsensitiveFlag) for stable case-aware comparisons and str2:upperCase() / str2:lowerCase() for enforcing required casing.

<xsl:if test="str2:compare($row/@database_name,$database-name,not($server-case-sensitive)) = 0">
  <!-- same database (case-aware) -->
</xsl:if>

ContextOnly rules: execute SQL and handle errors

ContextOnly rules commonly execute a DMV or catalog query via ctx:executeQueryDirect() . They almost always handle $query-results/error , and only display detailed errors in design mode.

<xsl:variable name="query-results" select="ctx:executeQueryDirect($database-name ,$query-text)" />

<xsl:choose>
  <xsl:when test="$query-results/error">
    <xsl:if test="$is-design-mode">
      <xsl:for-each select="$query-results/error">
        <xsl:call-template name="output-message">
          <xsl:with-param name="msg"  select="$v-rulename" />
          <xsl:with-param name="desc" select="concat($v-rulename,' : ', text())" />
          <xsl:with-param name="near" select="@near" />
          <xsl:with-param name="line" select="@line" />
          <xsl:with-param name="column" select="@column" />
        </xsl:call-template>
      </xsl:for-each>
    </xsl:if>
  </xsl:when>
  <xsl:otherwise>
    <xsl:for-each select="$query-results/*">
      <xsl:variable name="row" select="." />
      <!-- Evaluate condition and emit finding -->
    </xsl:for-each>
  </xsl:otherwise>
</xsl:choose>

Building messages with concat and conditional fragments

Messages are generally composed with concat() . For optional fragments, some rules use helper functions like cmn:ifTrueElse() to avoid duplicated punctuation.

<xsl:variable name="key-columns-list"
  select="concat($row/@equality_columns,
                cmn:ifTrueElse(string-length($row/@equality_columns)&gt;0 and string-length($row/@inequality_columns)&gt;0,',',''),
                $row/@inequality_columns)" />

Working with file paths and WMI

Some rules use WMI to validate server-side file existence. When constructing WMI queries, backslashes are escaped.

<xsl:variable name="escaped-physical-device-name"
              select="str:replace($row/@physical_device_name,'','\')" />
<xsl:variable name="wmi-query-text"
              select="concat('Select * From CIM_Datafile Where Name = ',
                             $gc-apostroph, $escaped-physical-device-name, $gc-apostroph)" />
<xsl:variable name="wmi-query-result" select="wmi:queryWmi($wmi-query-text)" />

Guidelines

• Always anchor findings to the most specific AST node you can (use near-element when possible).

• Prefer cached selects for repeated XPath queries over the same large node set.

• In ContextOnly rules, keep queries deterministic and limit impact (many template rules use OPTION(MAXDOP 1) ).

• Gate noisy diagnostics behind $is-design-mode .

• When offering fixes, ensure the replacement is safe, minimal, and preserves whitespace when relevant.

Simple rule expression common patterns

Simple analysis rules in SQL Enlight use an XPath match expression (RuleType = XPathExpression) rather than full XSLT. The engine evaluates the expression and reports a finding for each matched node. This section documents common patterns used to write effective, fast, and readable match expressions.

How simple match expressions work

A simple rule expression must evaluate to a node-set. Each matched node becomes a rule finding, and the engine automatically supplies boilerplate (message/severity/location) based on the rule definition.

Keep the expression focused on returning the most specific node you want highlighted (a keyword token, identifier node, literal, etc.), rather than returning a broad statement node.

Return the node-set you want to flag

The simplest form is a single XPath that ends at the node to report.

<!-- Match numeric literals used in ORDER BY -->
$batch-statements/k:order/g:commalist/g:expression/co:exact-number[ancestor::g:statement[1]]

Tip: prefer ending on a leaf node (e.g., co:exact-number ) so the Error List highlights precisely.

Scope by statement type with sqml:is()

Most Batch rules start from $batch-statements , restrict by the parser statement type ( @type ), then navigate to a token or clause.

<!-- Match SET ROWCOUNT statements -->
$batch-statements[sqml:is('SetSessionHandling', @type)]/k:set/k:rowcount

You can include multiple statement types by listing them in the first argument (space-separated).

<!-- Match any statement whose type is one of the listed values -->
$batch-statements[sqml:is('UpdateSearched InsertValues DeleteSearched', @type)]//k:where

Use predicates for filters

Predicates ( [...] ) are the primary way to exclude false positives. Prefer small, explicit predicates.

<!-- Example: match USE statements only when they are not the first batch statement -->
$batch-statements[sqml:is('Use', @type)]
  [ctx:getBatchIndex() != 0 or preceding::g:statement[parent::pu:semicolon]]

Common predicate building blocks:

• not(...) to exclude contexts

• ancestor:: , preceding:: , following:: to reason about structure and ordering

• string-length(...) to test presence of values

Batch boundary and ordering checks

Use ctx:getBatchIndex() and axes such as preceding:: together with batch separators (e.g., pu:semicolon ) to enforce “first statement” or “only in first batch” rules.

<!-- Match non-USE statements when they appear before the first batch separator -->
$batch-statements[ctx:getBatchIndex() = 0]
  [not(preceding::g:statement[parent::pu:semicolon])]
  [not(sqml:is('Use', @type))]

Match alternatives with || (OR)

If the same issue can be represented by different paths, combine them with || . The engine evaluates each path and unions the results.

<!-- Match not-trusted constraints from multiple locations -->
$database/Constraints/Constraint[@IsNotTrusted='true']
  || $database/ForeignKeys/ForeignKeyConstraint[@IsNotTrusted='true']

Enhanced chaining with local variables (=> $local =>)

For complex matches that would normally require nested loops in XSLT, SQL Enlight simple rules support an enhanced chaining syntax:

XPath1 => $local1 => XPath2 => $local2 => XPath3 => $match

Conceptually, each step iterates the node-set from the previous step. The local variables reference the current node ( . ) at that stage. The final $match node-set is reported.

<!-- Skeleton example (illustrative): find multiline comments, then flag those containing a phrase -->
ctx:getCachedSelect('$batch//cmt:*')/self::cmt:multiline
  => $comment
  => .[regexp:test('(?i)\b(TODO|HACK|UNDONE)\b', string(.))]
  => $match

Performance with ctx:getCachedSelect()

If an expression starts from an expensive selection (e.g., “all comments in batch”, “all identifiers”), cache it:

<!-- Cache a base selection once, then filter -->
ctx:getCachedSelect('$batch//cmt:*')/self::cmt:multiline

Parameters in simple rules

In simple expressions, rule parameters are available as variables (no need for the $parameters/Param boilerplate). Use them inside predicates to make the rule configurable.

<!-- Example: toggle matching based on a boolean parameter -->
$batch-statements//i:identifier[$EnableRule = true()]

Tip: Keep parameter names short and descriptive; they become variable names in expressions and are easy to mistype.