What is an Index. How to create and Index and How many types of Indexes are there?

Problem: What is an Index. How to create and Index and How many types of Indexes are there?

Solution: CREATE INDEX

If you have a reading habit and most books have a page called contents where the author or editor will specify the page numbers for a particular chapter and sub chapters to get quick access of the contents you are looking for. Similarly, the data in a table needs some sorting required for retrieving data as quickly as possible.

Index in SQL Server means the physical order of storing the data in a table. An Index can be used to speed up the performance of a query by reducing the number of pages used to store the data.

Pages are the fundamental unit of data storage in SQL Server. Each page is 8kb and a collection of 8 pages is called an extent. This means SQL Server has (1024/8)128 pages and (128/8) 16 extents for a megabyte of memory. The reason I am explaining about the pages is because an index can reduce this number of pages when we create one and help in search through these pages easily.

These are the few common types of indexes which are available in SQL Server.

·         Clustered Index- The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. A table can only have one clustered Index.

·         Non Clustered Index - Each index row in the non-clustered index contains the non-clustered key value and a row locator. In non-clustered there are two sub types.

o   Filtered Index- A non-clustered index that is extended to include non-key columns in addition to the key columns.

o   Included columns- It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Unique Index- A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.

Example:

Example 1: In this example I would like to create few varieties of indexes based on the need they can be used in the tables or indexes.

--clustered index, this will create a primary key and unique clustered index on the column.

ALTER TABLE [dbo].[Employees] ADD  CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

 ([EmployeeID])

  --unique index. This will allow to maintain unique rows for combination of empid, name,phoennumber when you don’t want to make duplicate entries.

CREATE UNIQUE INDEX AK_Name_PhoneNumber ON [dbo].[Employees]

  ([EmployeeID],[Name],[PhoneNumber])

  -- filtered index. This will boost when searched for not null company_id in where clause.

CREATE NONCLUSTERED INDEX fIX_CompanyID ON [dbo].[Employees]

                  ([EmployeeID],[Name],[PhoneNumber],Company_ID)

                WHERE Company_ID IS NOT NULL

--covering index this will cover the column company_id when searched in predicates.

CREATE NONCLUSTERED INDEX [ix_name_phonenumber] ON [dbo].[Employees]

(

                [EmployeeID] ASC,

                [PhoneNumber] ASC,

)

INCLUDE (Company_ID)

As you can see the above screenshot the indexes are created and in the indexes folder when you navigate through SSMS.

Also if you want to locate or find all indexes on a table or index. Please go through this article

how-to-know-what-indexes-are-on-table.

In the next article we are going to talk about when to create an Index and when not to create an Index: