How to create Index in the table:-
Non Clustered Index:-
Suppose we have a table tbl_Student and the Sql script for creating this table is given below:-
(
[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]
)
Suppose it contains the following data-
Now First we can check whether the table contains any index or not.For this we can use the following queries:
sp_helpindex tbl_student
select name from sysindexes where id=object_id('tbl_student')
Now, since we don't make any index on the table tbl_student yet, so when we run the sql statement "sp_helpindex tbl_student" , it will give us the following result:-
The object 'tbl_student' does not have any indexes, or you do not have permissions.
Now , we can make the index on this table by using the following Sql Statement:-
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
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://vivekjohari.blogspot.com/2010/01/difference-between-clustered-and.html
