Indexes in Sql server

Indexes-Indexing  is way to sort and search records in the table. It will improve the speed of locating and retrieval of records from the table.It can be compared with the index which we use in the book to search a particular record.
In Sql Server there are two types of Index

1) Clustered Index
2) Non Clustered Index

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:-

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]
)  

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:- 

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

Create Index Index_Firstname on tbl_student(FirstName)  

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:-

 

Create Clustered Index (Index name ) on Table Name (Column Name)

For Example, 

 

Create clustered Index Index_Studentid on tbl_student(Studentid)     

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,

 

Drop Index Index_Firstname on Tbl_student

 

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  

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Most Imp SQL Concepts, SQL Indexes, SQL Server and tagged , . Bookmark the permalink.

25 Responses to Indexes in Sql server

  1. Pingback: Lexus Auto Repair Service Near Greensboro NC

  2. Pingback: taraftarium24

  3. Pingback: scam

  4. Pingback: http://apartamentynaibizie.pl/przybysze-z-ciemnosci-tom-2-kontratak-sf-7-2013-1879.php

  5. Pingback: โกงชักดาบ

  6. Pingback: https://biznesblog.biz.pl/co-zrobic-gdy-klient-nie-placi-faktur/

  7. Pingback: link bong88

  8. Pingback: Website

  9. Pingback: https://biznesblog.biz.pl/forum/

  10. Pingback: cbd

  11. Pingback: quantitative marketing research

  12. Pingback: situs dominoqq online

  13. Pingback: mega poker99

  14. Pingback: Polska dla Jezusa, Marsz dla Jezusa,

  15. Pingback: www.parisqq1.site

  16. Pingback: elang qq

  17. Pingback: lapakqq

  18. Pingback: Testogen testosterone booster

  19. Pingback: raja qq

  20. Pingback: daftar qiuqiu99

  21. Pingback: rajacapsa

  22. Pingback: poker99 online

  23. Pingback: jasaqq.club

  24. Pingback: www.cialisis.org

  25. Pingback: mejaqq

Leave a Reply