Custom Index Naming Policies

Aireforge uses a small script (similar in syntax to C#, Java, and Javascript) to generate the expected names for each index, which is then used to validate existing index names and create names for new indexes. It comes with several built-in scripts that you can use, or you can create your own. This page explains the scripting language used and how you can create scripts to match your index naming convention.

Overview

Each time Aireforge needs to generate a name for an index, it runs the script, automatically passing in many of the index properties as variables for the script to test or use to generate the index name. The script then returns the index name, which Aireforge then uses. A simple but not uncommon example of an index naming script is shown below.

var _indexName;

if (_isPrimaryKey) {
    _indexName = "PK";
} 
else 
{
    _indexName = "IX";
}

_indexName += "_";    
_indexName += _tableName;
_indexName += "_";
_indexName += join(_indexedColumns, "_");
    
return _indexName;

Breaking down this script line by line:

var _indexName;

This declares a variable called "_indexName" that we will be using to store the name for our index.

if (_isPrimaryKey) 
{
    _indexName = "PK";

The first of these lines is an "if" statement, testing the passed in "isPrimaryKey" variable. If _isPrimaryKey is true (i.e., the index is a primary key index), then the following line will be processed, which assigns the indexName variable the value of "PK". In less technical terms, this is saying "if the index is a primary key, then the index name should start with "PK_".

} 
else 
{
    _indexName = "IX";
}

If the "if" statement condition doesn't equal true, then the "else" block will be followed. In this case, if this example, if the index isn't a primary key index, the index name should start with "IX".

_indexName += "_"; 

The "+=" operator is a way of appending to or incrementing a variable. If the variable is a number, then using += will add to that number. If the variable is a string, as in this case, += will append to the string. In this example, this line is simply appending an underscore to the index name generated so far, so our "PK" or "IX" will become "PK_" or "IX_".

 _indexName += _tableName;

This line is appending the table name parameter, which was automatically passed into the index name being generated. So if our table is called "sales" and the index is a primary key index, the index name so far will be "PK_sales",

_indexName += "_";

This is appending another underscore to the index name.

_indexName += join(_indexedColumns, "_");

This line is the most complex that you will encounter in this example. It shows a call to a function called "join", passing in two parameters. The first parameter is the _indexedColumns variable (which is an array of strings), the second is an underscore. The join function will use these two parameters to generate a string variable and return it. So if our "sales" table has three columns "id", "date", "amount", the returned string from the join function will be "id_date_amount". This returned string is then appended to the _indexName variable. So the index name generated for the primary key index on our "sales" table will be "PK_sales_id_date_amount".

return _indexName;

This line returns the generated index name out of the script back to Aireforge. Nothing after a "return" line is processed, so this line should always be the last in your script. So at this point, we will return to Aireforge, saying that the primary key index on the "sales" table should be called "PK_sales_id_date_amount". If your existing primary key index on this table isn't called this, then it will be shown as a warning in the Advise results. You should hopefully now have a basic understanding of how the script works, allowing you to start making changes to the built-in scripts. The following sections go into more detail about the scripting language.

---

Variables

Variables are declared using the "var" keyword, followed by the name you're giving the variable. e.g.

var _a;    
var _b;

Note that the leading underscore isn't required for variable names, but it is a simple and effective way of making them stand out unambiguously as variables. So you could simply have:

var a;
var b;

You can also declare a variable and assign an initial value to it on the same line:

var _c = "carrots";

Variables are not declared with a fixed type, although variables are typed. The type is inferred by what is assigned to the variable. Four variable types are supported in scripts: numbers, booleans, strings, and string arrays. They are declared as follows:

var numExample = 123;    
var numExample2 = 4.56;
var booleanExample = true;    
var booleanExample2 = false;
var stringExample = "one";    
var stringArrayExample = ["one", "two", "three"];

Until a variable has a value assigned, it has an "empty" value, which counts as false, zero, an empty string, or an empty array depending on how it's used.

Operations performed on variables will treat the variables in the most appropriate way for their type, but will try treating them as other types if necessary. So adding two numbers together will result in the sum of the numbers because both variables are numbers, but adding a number to a string will result in them both being treated as a string.

var numOne = 1;
var numTwo = 2;
var sum = numOne + numTwo; //this variable will be a number with value 3

var numOne = 1;
var strTwo = "2";
var sum = numOne + strTwo; //this varlable will be a string with value "12" (from appending "1" and "2" together);

Index Property Variables

The index property variables available to use are:

• _tableName - a string variable containing the name of the owning table (without schema name).
• _isPrimaryKey - a Boolean variable indicating if the index is a primary key index or not.
• _isClusteredIndex - a Boolean indicating if the index is a clustered index.
• _isUniqueIndex - a Boolean indicating if the index is a unique index.
• _isUniqueConstraint - a Boolean indicating if the index is a unique constraint.
• _IndexedColumns - a string array variable containing the names of the columns indexed.
• _includedColumns - a string array variable containing the names of any included columns.
• _indexDescription - a placeholder variable for use if your index naming convention includes a free-text description of what the index is for (for more information, see below).

The _indexDescription variable is for use if your index naming convention includes a free-text description of what the index is for, rather than just being based on the index properties. This variable should be appended to your generated index name at the point where you would have the free-text description. When your generated index name is returned to Aireforge, it detects that _indexDescription placeholder and validates index names as best it can, validating anything before and after this, and assuming anything in-between is the free-text description.

For example, if you have indexes called "IX_date_MakesSearchingByDateFaster" and "IX_id_MakesSearchingByIdFaster", then your script would use the _indexDescription like this:

var _indexName = "IX_";
_indexName += join(_indexedColumns, "_");
_indexName += _indexDescription;
return _indexName;

---

If Statements

If statements allow you to test variables and perform different outcomes based on the results. The simplest if statements are of the form:

if (<some condition is true>) 
{
    <do something>
}

Where is a test of one or more variables.

If statements can be extended with an "else" block:

if (<some condition is true>) 
{
    <do something>
} 
else 
{
    <or do this if it's not true>
}

It's also possible to chain multiple if statements together to create more than two outcomes:

if (<some condition is true>) 
{
    <do something>
} 
else if (<a second condition test>) 
{
    <do something else>
} else 
{
    <or do this if neither of the above is true>
}

Supported binary operators are:

Operator	Description
==	is equal
!=	not equal
&&	and
||	or
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to

---

Functions

There are several built-in functions that you can use to manipulate the index properties and variables, each of which works slightly differently depending on the type of variable(s) passed in:

len(x)

This returns a number giving the length of the parameter passed in. That parameter may be a string, in which case the length of the string (i.e. number of letters) is returned, or an array, in which case the length of the array is returned.

uppercase(x)

This function takes a single parameter and returns it as an uppercase string. That parameter may be a string or string array. If called with a string, the uppercase version of that string is returned. If called with an array, a new array is returned, with each element being made uppercase.

lowercase(x)

This simply does the same as the uppercase function but converts to lowercase.

join(x, y)

This function joins the contents of array x using y as a delimiter. e.g.:

var arr = ["one", "two", "three"];    
var joined = join(arr, "***"); //this variable will be a string with value "one***two***three"

replace(x, y, z)

This function takes parameter x and replaces all occurrences of y with z. Parameter x can be a string or string array.

var strOne = "apple";
var strTwo = replace(strOne, "p", "t"); //this variable will be a string with value "attle";

var arrOne = ["one", "two", "three"];
var arrTwo = replace(arrOne", "o", "p"); //this variable will be an array with elements "pne", "twp", "three"

substring(x, y, z)

This function takes a string parameter x and returns a portion of that string according to number parameter y, which defines the start index, and optional number parameter z, which defines the length of string to return.

var strOne = "apple";    
var strTwo = substring(strOne, 3); //this variable will be a string with value "le";

var strThree = "tomato";    
var strFour = substring(strThree, 2, 3); //this variable will be a string with value "mat";

---

General Notes

Case

Scripts are case sensitive, which includes keywords, variable names, and strings.

• So you must use "if" to start an if statement, not "IF".
• You must be consistent in using variable names. So if you declare variable "indexName", you must refer to it in the case from then on.
• Comparing "test" to "TEST" in an if condition will return false, as the case is factored into the comparison.

Line Termination

Although the script parser does not enforce that all lines should end with a semi-colon ( ; ), it's good practice, as it removes any ambiguity about where an expression starts and ends.

Comments

You can add comments to scripts in two ways, block comments or single-line comments.

//this is a single-line comment, commenting out everything after the two slashes
/*this is a block comment, commenting out everything
between the opening and closing markers*/