|TSQL ->||6 ideas than can help to improve Index efficiency|
List of 6 ideas than can help you to improve
efficiency of standart (Nonclustered) Index. Some of the following tips are
relevant to MS-Access database and other DBMS systems. Imagine table like this,
(we talk about big tables):
 Use apropriate datatypes for each column. For example: We know that house number value in our dataset can not exceed 1500. So using INT datatype, that allows storage of value up to 2147483647 and reserves 32-bit of memory, look like a waste of resources. We will change datatype of [house] column to SMALLINT (max value 32767) that reserves only 16-bit in memory. Deminishing space that occupies one column will save space that table allocates on disk and will cause index to be more compact.
 Use NOT NULL - constraint. Each column that participates in index we will define as [NOT NULL], if possible.
 Column precedence in index metters! For our query [Index Seek] can be achived only if [city] column will be first column in index, so we will fix it.
 Prefer to use "covering" index whenever possible. Covering - means that all data that we need is stored inside index. For our query, index [idx1_Address] is covering. But if such index does not exists for the kind of query you execute offen, consider to create it.
 Use directive FILLFACTOR = 100(%) for index on read-only table in order to ensure maximal page usage, and cause index to be more compact.
 If your table is not read only, do not forget to Reorganize or Rebuild Index from time to time as a part of database maintanance. Update/Insert/Delete activity on table records, will inevitable cause Index data fragmentation, which sooner or later will degrade Index performance.