How To: Create Analysis Rule with Rule Designer

Create new rule

The Creation of analysis rule can be started from the SQL Enlight Active Analysis Rules list, from SQL Editor, and from SSMS Object Explorer.

Create a new analysis rule from Active Analysis Rules

  1. In SSMS or Visual Studio, open SQL Enlight Options and go to the Analysis Rules page:

    create-analysis-rule-3

    A new analysis rule can be created either from the toolbar or from the context menu of the rules list.

    When the rule is created from the toolbar, using the New Rule menu drop-down list, the rule is not associated with any group.

    To create a rule in a specific group:

  2. Select an analysis group which will be the parent group of the rule

  3. Open the group’s context menu and use the New Rule menu item and select the desired rule type.

  4. The Analysis Rule Designer will open where the rule properties and match expression can be provided. See Analysis Rule Designer for details.

  5. Set the main properties of the analysis rule such as name, message, and description.

  6. Provide a Test SQL script for the code analysis rules.

    For database analysis rules or code rules that need a database metadata, or server metadata, make sure there is valid Analysis Context and valid SQL Connection.

  7. Craft and test the rule expression to match the desired code or context elements.

    Refer to Analysis Rule Designer for information about the analysis rule expression in the rule designer.

Create a new Variant analysis rule

A Variant analysis rule can be created by selecting the rule in the Analysis Rules list and selecting the New -> Variant Rule from the context menu.

create-analysis-rule-3a

The variant rule name can be changed, but by default, it will be the same as the source rule name combined with the number of rule’s variants.

Any properties of the rule except the rule expression can be changed in the variant rule.

Create a new code analysis rule in SQL Editor

Creating a SQL code analysis rule from the SQL Editor has some advantages over creating a rule in the Active Rules list:

  1. The selected SQL token is used a rule’s match expression.

    The batch in which the selected target SQL token resides is transferred to the analysis rule designer and used as Test SQL.

    The editor’s active SQL connection is brought to the rule designer and used for the analysis context and when testing the rule.

  1. Open SQL Editor in SQL Server Management Studio and select, or place the cursor on select a token that you wish to be matched by the rule.

  2. Open the SQL Editor’s context menu and choose one of the commands – Create Simple Analysis Rule … or Create Simple Advanced Rule….

    sql-editor-context-menu
  3. Analysis Rule Designer will open.

    – The SQL code from the current batch will be preset as aTest SQL script.

    – The match expression will be preset with the XPath to the selected token.

    – The SQL Connection will be preset with the connection from the SQL Editor and Test Analysis Context will be loaded from this connection.

  4. Validate, test, and tune the match expression to get expected results.

  5. Save the rule, activate it on the Active Rules page, and assign it to an analysis group.

Create a new database context analysis rule from Object Explorer SQL Editor

Creating a database context analysis rule from the Object Explorer will preset the match expression with the XPath to a selected database object.

  1. Open Object Explorer in SQL Server Management Studio, select a database or server object that you wish to be matched by the rule.

  2. From the Static Analysis context menu of the selected node, choose one of the commands – Create Simple Analysis Rule … or Create Simple Advanced Rule….

    object-explorer-create-rule-context-menu
  3. Analysis Rule Designer will open.

    – The SQL code from the current batch will be preset as a Test SQL script.

    – The match expression will be preset with the XPath to the selected database or server object.

    – The SQL Connection will be preset with the connection from the Object Explorer and Test Analysis Context will be loaded from this connection.

  4. Validate, test, and tune the match expression to get expected results.

  5. Save the rule, activate it on the Active Rules page, and assign it to an analysis group.

See Also