Analysis Rule

The Database rules can be used to only analyze the current database schema. This kind of rule does not depend on the analyzed T-SQL code, but only on the current connection analysis context.
The Analysis Context XML can be referenced in the rule expression using the following XSLT variables:

• $context – references the root Context XML element.

• $server – references the Server XML element.

• $database – references the Database XML element.

Important for the Database analysis rules is that they are evaluated only once at the end of the analysis process.

Candidates for Database rules are all the analysis rules which do not refer to the syntax tree but only use the current analysis context.

The Batch analysis rules also have access to the analysis context but are focused on the T-SQL code its SQML syntax tree representation. The code rules are applied separately for each of the analyzed T-SQL batches.

For specific types of checks might be convenient to have separate implementations for both rule types to cover both database schema and T-SQL code violations.

An example of such a rule which supports both context and code analysis is rule SA0043.

The rule has one implementation – SA0043A that checks the current database schema for the usage of the obsolete data types and another implementation – SA0043A which checks the T-SQL code for usage of those types.

The Variant rule is a separate rule type.
It allows the creation of rule variants that use the rule expression of the main rule, but to have some customized rule properties such as name, descriptions, and parameters.
The variant rules do not have their rule expression but use the expression of the main rule.
This way if any changes are made to the main rule expression the changes will be reflected in the rule variants.

The explicit rules are not a separate rule type, but just rules added to the Explicit Rules group.

This group is meant to store all the rules which are meant to be executed separately, because the results they produce generally (but not necessarily) are more informational than just indicating defects or showing possible problems.

Dynamic rules are the rules which use direct queries to the underlying database to retrieve analysis data and generate results.

These kinds of rules mainly process and output the server state information returned by the SQL Server dynamic management views and functions.

The information can be used to monitor the health of a server instance, diagnose problems, and tune performance.

The Advanced Analysis Rule expression can be defined using the standard XSLT syntax, but with some restrictions to the allowed XSLT elements.

The restrictions are meant to disallow output results that are not valid according to the SQL Enlight analysis engine.

Almost all elements of the XSLT standard syntax are allowed except elements that can directly manipulate the result data like:

xsl:call-template,
xsl:value-of,
xsl:element,
xsl:attribute and etc.

These elements are allowed as children of: xsl:variable, xsl:with-param, and xsl:param.

Additional to the standard XSLT and XPath functions, SQL Enlight provides several extension functions that can be used inside the rule expression. See XSLT Extension Functions for a list of some of the supported extensions.

A full list of supported XSLT and XPath functions can also be found in the Snippets component that appears next to the rule expression editor.

Example of an Advanced expression which matches the SET ROWCOUNT statement can be defined like this:

 1<xsl:for-each select="$batch-statements[contains('3090',@type)]/k:set/k:rowcount">
 2<xsl:variable name="rowcount" select=".">
 3
 4<xsl:call-template name="output-message">
 5<xsl:with-param name="msg" select="$v-rulename"/>
 6<xsl:with-param name="desc" select="concat($v-rulename,' : ',$v-ruledescription)"/>
 7<xsl:with-param name="near-element" select="$rowcount"/>
 8<xsl:with-param name="type" select="$v-ruleseverity"/>
 9</xsl:call-template>
10</xsl:for-each>

The call to the output-message writes the rule details as well as the source string of the rule violation.

Since version 2.0 of SQL Enlight, it is possible to create rules using Simple Analysis Rule expressions that use only XPath to match particular elements in the syntax tree or the database analysis context.

SQL Enlight provides several extension functions which can be used inside the rule expression.
A full list of supported XSLT and XPath functions can be found in the Snippets component that appears next to the rule expression editor.

An example of a Simple expression that matches the SET ROWCOUNT statement can be defined like this:

1$batch-statements[contains('3090',@type)]/k:set/k:rowcount

While the simple rules expressions are very easy to write with the help of the Match Suggestions, they can be very inefficient and cumbersome in some cases.
For example, when writing an XPath expression that has to evaluate and compare nodes from different contexts, the XPath expression by itself, has no way to store intermediate results and use those in later comparison.

To target this problem, SQL Enlight provides an enhancement to the Simple XPath expressions, which makes them almost as powerful as the Advanced Analysis Rules, which use XSLT.
The enhanced syntax allows usage of local variables as well as storing and comparing nodes from different contexts.

1XPathExpression1 => $local1 => XPathExpression2 => $local2 => XPathExpression3 => $match

Each of the XPathExpression-s in the syntax example can access and use the value of the preceding local variables. The only requirement is that the XPath expressions must evaluate to a node-set.

This enhanced syntax is translated to nested XSLT for-each statements, while the current context node in each loop is stored in a local variable with a provided name.
Later the variable is accessible and can be used in @select-s of the inner for-each statements.
If the last variable is skipped, it is automatically set to the $match variable, which is later evaluated as a result.

 1<xsl:for-each select="XPathExpression1">
 2    <xsl:variable name="local1" select="."/>
 3    <xsl:for-each select="XPathExpression2">
 4        <xsl:variable name="local2" select="."/>
 5        <xsl:for-each select="XPathExpression3">
 6            <xsl:variable name="match" select="."/>
 7                          <xsl:call-template name="output-message">
 8                          ...
 9                          <xsl:with-param name="near-element" select="$match" />
10                          ...
11                          </xsl:call-template>
12        </xsl:for-each>
13    </xsl:for-each>
14</xsl:for-each>

Select all FROM clauses in the SELECT,UPDATE and DELETE statements.

1<xsl:for-each select="$batch-statements[contains('3511,3512,3531,3532,3541,3542',@type)]//k:from[parent::k:select]">
2  <xsl:variable name="from-clause" select="."/>

Generate a unique id to be used for restricting the scope of the FROM clause and identifying the parent FROM clause of a given table source.

1<xsl:variable name="from-clause-id" select="generate-id($from-clause)"/>

Select table source container elements.

1<xsl:variable name="table-source-containers" select="$from-clause/g:commalist 
2                                                  |  $from-clause//k:join"/>

Select and iterate the single or multipart identifiers which are direct children of the table source containers.

 1<xsl:for-each select="$table-source-containers/*[self::pu:dot or self::i:* or self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]]">
 2  <xsl:variable name="table-source" select="."/>
 3
 4  <!-- If the table is aliased, the hints are under the AS keyword element, otherwise the hints are under the identifier element. -->
 5  <xsl:variable name="table-hint-target" select="$table-source[not(self::pu:dot)]
 6                                             | $table-source/self::pu:dot/descendant-or-self::pu:dot[not(pu:dot)][1]/i:*[last()]"/>
 7
 8  <!-- Check for existence each possible way of setting a table hint. -->
 9  <xsl:if test="$target-table-source
10          [not(./g:brackets/g:commalist/i:common[str2:compare(@name,'nolock',true())=0])]
11          [not(./g:brackets/g:commalist/g:expression/i:common[str2:compare(@name,'nolock',true())=0])]            
12          [not( ./g:brackets/g:commalist/pr:hint[@name='nolock'])]
13          [not( ./k:with/g:brackets/g:commalist/pr:hint[@name='nolock'] )]">
14
15    <xsl:variable name="table-identifier" select="$table-hint-target/self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]
16                      | $table-hint-target/self::i:*"/>

The hint was not matched and we return rule violation information by calling the “output-message” template.

 1      <xsl:call-template name="output-message">
 2        <xsl:with-param name="msg" select="$v-rulename"/>
 3        <xsl:with-param name="desc" select="concat($v-rulename,' : ', $v-ruledescription)"/>
 4        <xsl:with-param name="near-element" select="$table-identifier"/>
 5        <xsl:with-param name="type" select="$v-ruleseverity"/>
 6      </xsl:call-template>
 7    </xsl:if>
 8  </xsl:for-each>
 9
10</xsl:for-each>

A direct translation of the Advanced expression to an Enhanced Simple expression will look like this:

 1$batch-statements[contains('3511,3512,3531,3532,3541,3542',@type)]//k:from[parent::k:select] => 
 2    $from-clause => 
 3($from-clause/g:commalist |  $from-clause//k:join)/*[self::pu:dot or self::i:* or self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot]] => 
 4    $table-source =>
 5($table-source[not(self::pu:dot)] | $table-source/self::pu:dot/descendant-or-self::pu:dot[not(pu:dot)][1]/i:*[last()]) => 
 6    $target-table-source =>
 7$target-table-source[not(./g:brackets/g:commalist/i:common[str2:compare(@name,'nolock',true())=0])]
 8                            [not(./g:brackets/g:commalist/g:expression/i:common[str2:compare(@name,'nolock',true())=0])]                        
 9                            [not( ./g:brackets/g:commalist/pr:hint[@name='nolock'])]
10                            [not( ./k:with/g:brackets/g:commalist/pr:hint[@name='nolock'] )] => 
11    $table-hint-target =>
12$table-hint-target/self::k:as/*[not(self::cmt:*)][1]/self::*[self::i:* or self::pu:dot] | $table-hint-target/self::i:* => 
13    $table-identifier

The fix for an advanced analysis rule can be defined using the fix-replace, fix-append-before, fix-append-after, and fix-targets parameters of the output-message template call.

Targeting other elements than the matched by the rule

1<xsl:with-param name="fix-target" select="$other-target-element" />

Replacing matched target element

1<xsl:with-param name="fix-replace" select="'SCOPE_IDENTITY()'"/>

Append text before the target element

1<xsl:with-param name="fix-append-before" select="'DECOMPRESS('" />

Append text after the target element

1<xsl:with-param name="fix-append-after" select="')'" />

The fix for a simple analysis rule can be defined by providing strings for replacing or appending to the matched by the rule syntax tree or database context nodes.

The Simple analysis rule fix is always applied on the matched by the rule node, which is a difference to the advanced rule expressions where fix targets can be defined.

Similar to the advanced analysis rules, the simple rule has three string properties for defining a fix:

• Fix Append Before – the string set to this property will be appended before the text of the matched node

• Fix Append After – the string set to this property will be appended after the text of the matched node

• Fix Replace – the string set to this property will replace the text of the matched node

The fix properties can be any string literal, but may also contain interpolated expressions.

The interpolated expressions can be any XPath expression that evaluates to string and is enclosed in backticks `XPath expression`.

For example, the name of the matched column will be added as a parameter to the DECOMPRESS function:

1DECOMPRESS(`,$match/@name,`)