|
Normalise
the database tables to 3rd Normal Form.
Define Primary Keys for each table.
Enforce Foreign Key relationships via
Foreign Key constraints.
Define
indexes to support the searchable arguments in the Select statement’s
WHERE clause.
Do not over index a table – keep the number of indexes to 4 or below
Do not create a clustered index on an auto-incrementing column (eg.
Identity field). Use a non-clustered index for this type of column.
Create a clustered index on a column/s that are used in frequently
executed range retrievals.
Indexes should Not be created if
-
the
index is not used by the query optimizer. Use Query Analyzer's
"Show Execution Plan" option to see if your queries against
a particular table use an index or not. If the table is small, most
likely indexes will not be used.
-
the
column values exhibit low selectivity, often less than 90%-95% for
non-clustered indexes.
-
the
column(s) to be indexed are very wide.
-
the
column(s) are defined as TEXT, NTEXT, or IMAGE data types.
-
the
table is rarely queried.
Columns
used for the joins should have an appropriate index.
Only
use Unicode data types for columns if double-bytes characters will be
stored.
Use
a Varchar data type for a character type column if more than 8 characters
will be stored, otherwise use the Char data type.
|
