An Index is one of the most powerful techniques to work with this enormous information. Database tables are not enough for getting the data efficiently in case of a huge amount of data. In order to get the data quickly we need to index the column in a table.
An index is a database object that is created and maintained by the DBMS. Indexed columns are ordered or sorted so that data searching is extremely fast. An index can be applied on a column or a view. A table can have more than one index.
Types of Index: Microsoft SQL Server has two types of indexes. These are:
Clustered Index: A Clustered Index sorts and stores the data in the table based on keys. A Clustered Index can be defined only once per table in the SQL Server Database, because the data rows can be sorted in only one order. Text, nText and Image data are not allowed as a Clustered index.
SET STATISTICS IO ON
SELECT * FROM Employee WHERE EmpID = 20001
EmpID EmpName Cell Dept
20001 Black Smith 12345678901 1
Non-Clustered Index: Non Clustered Indexes or simply indexes are created outside of the table. SQL Server supports 999 Non-Clustered per table and each Non-Clustered can have up to 1023 columns. A Non-Clustered Index does not support the Text, nText and Image data types.
CREATE NONCLUSTERED INDEX NCL_ID ON Employee(DeptID)
SET STATISTICS IO ON
SELECT * FROM Employee WHERE DeptID = 20001
EmpID EmpName Cell Dept
40001 Black Smith 12345678901 20001
