Extend Microsoft Access Applications to the Cloud: Creating a Blank Web App and Using Templates

Adding indexing

If you create a new table, the primary key for the table will be indexed, and if you add a lookup, the foreign key for the lookup will also be indexed. The indexing on foreign keys is created to help improve performance when joining together two tables of data to return with queries.

Adding additional indexes is useful when you have a commonly searched field and you believe an index could help to retrieve the data. An index can also be created that is unique, and then you can use the index to enforce a business rule ensuring unique values.

Take a look at the design of the Employees template table. If you click the Indexes button on the Design contextual ribbon tab, Access opens the Indexes dialog box and displays all indexes defined in the table, as shown in Figure 4-14.

FIGURE 4-14 Displaying indexing on the Employees table.

In Figure 4-14, although the PrimaryKey index is unique, the Employee Number index is also unique. This means that the second index is enforcing a business rule stating that each employee has a unique employee number.

TIP

A value of NULL (no value) is treated as also being unique, so this index seems to allow one employee, and only one, to have no value for the employee number. But because the field is a required field (NOT NULL), there is no inconsistency. If you created a field that was not required (NULLABLE) and created a unique index, you could get some strange side effects.

If you have an activity that is slow to execute, adding an index on, for example, a field used for filtering the data can improve performance. The only way to test this is to add the index and then see if the performance has improved. Indexes can also be created using multiple fields. One key question to ask before adding an index is, “Will the field values have sufficiently varied values to make the index selective?” For example, indexing a field with only four or five distinct values is unlikely to be of great benefit, while indexing a field for a postal code is likely to be beneficial if you are constantly filtering or searching on this field.

You also incur a cost with having indexes: the database will need to update the indexes when data changes. This means that adding many indexes to a table can result in operations to change the data slowing down (because the indexes will need to be updated). It is a good idea not to create too many indexes. Exactly what is meant by too many is difficult to quantify. However, if you kept the number of indexes to 5 or 6, that would not be an unreasonable number of indexes.

TIP

Although the Microsoft Azure SQL Database has tools for displaying how indexing is being used (specifically, showing the execution plan for your SQL), unfortunately at the time of this writing, you will not have sufficient permissions in Microsoft Azure SQL Database to use these features.