Index in SQL Server

What is an Index?

The index is used by queries to fetch the data from the tables quickly. Indexes are created on Tables and views.

Let us assume Index in SQL server to the index in a Textbook. If we want to go directly to a particular topic in the textbook, we refer the index for the page number and locate the topic. If there is no index in the textbook, we will have to check each and every page.

Similarly, if we create an index on the column of a table, it would be easy to fetch the records from the table. If not, the query engine will scan every record in the table which is called as Table scan. Table scan is bad for performance.

Create an index:

Let us assume a table emp as below.

Syntax: 

create index IX_tablename_columnname

on tablename(columnname order)

Here order indicates either Ascending or Descending.

Now let us create an index on salary column in emp table.

create index IX_emp_salary

on emp(salary asc)

We can check index under indexes folder under the table in object explorer or use ‘SP_HelpIndex’.