Some times ago I've written a post on how
to improve code performances of your Navision-based solution with the
new 4.0 SP1 release. Today I want to signal two interesting
properties about index management but that are not so visible (I'm
observing that lots of Navision developers don't know their existence).
With Navision 4.0 SP1, if you open a table with Object
Designer and check its keys, you can see two new properties: Clustered
and SQLIndex.
These are new interesting properties useful for a better index management.
With the Clustered property, you can determine
whether or not the index is clustered.
With the SQLIndex property, you can define the
fields that are used in the corresponding index on SQL Server. If the key
in question is not the primary key and you use the SQLIndex property to define
the index on SQL Server, the index that is created contains exactly the fields
that you specify and will not necessarily be a unique index. It will only be a
unique index if it contains all of the primary key fields.
Here a brief considerations on how to determine the best choice for the
Clustered property of your index (a brief resume from one of my
favourite SQL guides):
A nonclustered index is analogous to an index in a textbook.
The data is stored in one place, the index in another, with pointers to the
storage location of the data. The items in the index are stored in the order of
the index key values, but the information in the table is stored in a different
order (which can be dictated by a clustered index). If no clustered index is
created on the table, the rows are not guaranteed to be in any particular order.
SQL Server searches for a data value by searching the nonclustered index to find
the location of the data value in the table and then retrieves the data directly
from that location. This makes nonclustered indexes the optimal choice for exact
match queries because the index contains entries describing the exact location
in the table of the data values being searched for in the queries.
A clustered index determines the physical order of data in a
table (it's analogous for example to a telephone directory, where data are
sorted by last name). Because the clustered index dictates the
physical storage order of the data in the table, a table can contain only
one clustered index. A clustered index is particularly efficient on columns
that are often searched for ranges of values (after the row with the first value
is found using the clustered index, rows with subsequent indexed values are
guaranteed to be physically adjacent) or for finding a specific row when the
indexed value is unique.
Must be noted that PRIMARY KEY constraints create clustered indexes
automatically if no clustered index already exists on the table and it is
important to define the clustered index key with as few columns as possible.
So, when using a clustered or a nonclustered index? Generally I can say
this:
Use a nonclustered index for:
- Queries that do not return large result sets;
- Columns frequently involved in search conditions of a query (WHERE clause)
that return exact matches;
- Applications for which joins and grouping are frequently required. Create
multiple nonclustered indexes on columns involved in join and grouping
operations, and a clustered index on any foreign key columns;
- Covering all columns from one table in a given query.
Use a clustered index for:
- Queries that return large result sets;
- Columns that are accessed sequentially;
- Queries that return a range of values using operators such as BETWEEN,
>, >=, <, and <=;
- Columns that are frequently accessed by queries involving join or GROUP BY
clauses (typically these are foreign key columns). An index on the
column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for
SQL Server to sort the data because the rows are already sorted (this improves
query performance);
- where very fast single row lookup is required, typically by means of the
primary key. Create a clustered index on the primary key.
These are general recommendation that can help you to have a more efficient
and performant SQL Server system and with Navision 4 SP1 you have the
power to improve your performance control.