Indexes in Sql server

CREATE TABLE tbl_Student
(
    [StudentId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](150) ,
    [LastName] [nvarchar](150),
    [Phone] [nvarchar](20),
    [Email] [nvarchar](50),
    [StudentAddress] [nvarchar](200),
    [RegistrationDate] [datetime],
    [Enddate] [datetime]
)  

sp_helpindex tbl_student

 Create Index  (Index name) ON (Table name)(Column Name)

This sql statement will create a non clustered index “Index_Firstname” on the table tbl_student. We can also make a index on a combination of the columns of the table.This can be done by using the following Sql Statement:-    Create Index  (Index name) ON (Table name)(ColumnName1, ColumnName2)

  For Example:- 

 

Create Index Index_StudentName on tbl_student(FirstName, Lastname)   

This Sql Statement will create a non clustered Index Index_studentname on the combination of the two columns FirstName and LastName. 

 

Clustered Index:-  We can also create a Clustered index by using the given Sql Statement:-

 

For Example, 

 

The above Sql statement will create a Clustered index Index_Studentid on the table tbl_student. Now we can use the Sql Statements, which I described earlier to find out all the index made on the table tbl_student If we execute the statement “sp_helpindex tbl_student” , it will give us the following results:-   

 Droping an Index We can drop an Index by using the following Sql Statement:- Drop Index (Index Name) on (Table Name)  

  For Example,

 

The Above Sql Statement will delete the Index  Index_Firstname on the table tbl_student. Now if we execute the statement “sp_helpindex tbl_student” , it will give us the following results:-    

   There are some difference between the Clustered Index and Non Clustered Index.You can read these difference in my article “Difference between Clustered Index and Non Clustered Index”.You can also click on the given url to read this article   http://www.techmixing.com/2010/01/difference-between-clustered-and.html