Index in SQL SERVER

The SQL Server engine uses an index like a reader uses a book index. It helps to speed up the query output result.

Select * from T_Salary(nolock) where salary>4,000

Consider if the table T_Salary doesn't have any index. The query execution will query by scanning all the rows to find 3 persons having greater salary of 4,000.

Suppose if you create index like below:

Create index IDX_SAL on T_Salary (salary) with (Online=On)

[ Online = On - Works in Enterprise Edition]

Otherwise

Use Query Like Below:

Create index IDX_SAL on T_Salary (salary) with (Online=Off)
OR
Create index IDX_SAL on T_Salary (salary)

The above index will query the result without scanning the entire records or entire rows of the table.

This index also helps for Order by Clause, Group By Clause etc.

1. To see the indexes in Table:

EXEC sp_helpindex T_Salary

2. Index and Disk Space Required for Particular Table:

EXEC sp_spaceused T_Salary

3. To rename index:

EXEC sp_rename T_Salary.IDX_SAL, 'IDX_SALARY'

4. To Drop Index:

DROP INDEX T_Salary.IDX_SAL