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):

EmpNoFirstNameLastNamecitystreethouse
2gretgarboberlinszaks strasse102
4danieldeforomecorso vinchi25
9leonardorotamilanvia aligi10
12famkebacherberlinmuiler st.109
..................


Table structure:
CREATE TABLE [dbo].[Employees](
  [EmpNo] [int] NOT NULL,
  [FirstName] [nvarchar](20) NULL,
  [LastName] [nvarchar](30) NULL,
  [city] [nvarchar](50) NULL,
  [street] [nvarchar](50) NULL,
  [house] [int] NULL,
CONSTRAINT [PK_Employees1] PRIMARY KEY CLUSTERED
([EmpNo] ASC) ON [PRIMARY]
) ON [PRIMARY];
GO

-- Existing Index on Address columns
CREATE NONCLUSTERED INDEX [idx1_Address] ON [dbo].[Employees]
(
  [street] ASC,
  [city] ASC,
  [house] ASC
) ON [PRIMARY];
GO

-- suppose that we want the following query to run faster
SELECT EmpNo
FROM dbo.Employees
WHERE city = 'berlin' And street = 'hainz strauss' And house = 18;
GO

-- Drop Index
DROP INDEX [idx1_Address];
GO


[1] 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.

[2] Use NOT NULL - constraint. Each column that participates in index we will define as [NOT NULL], if possible.

ALTER TABLE dbo.Employees ALTER COLUMN city Varchar(50) NOT NULL;
ALTER TABLE dbo.Employees ALTER COLUMN street Varchar(50) NOT NULL;
ALTER TABLE dbo.Employees ALTER COLUMN house SMALLINT NOT NULL;


[3] 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.

[4] 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.

[5] Use directive FILLFACTOR = 100(%) for index on read-only table in order to ensure maximal page usage, and cause index to be more compact.

CREATE NONCLUSTERED INDEX [idx1_Address] ON [dbo].[Employees]
(
  [city] ASC,
  [street] ASC,
  [house] ASC
) WITH (FILLFACTOR=100) ON [PRIMARY]
GO



[6] 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.


sqlexamples.info