What is Indexes in Sql Server and Describe Clustered Index and NonClustered Index

What is Indexes in Sql Server

An index can be created in a table to increase the performance of application and we can get the data more quickly and efficiently. Suppose now we are reading book then I need to search each page of the book because I don’t know in which page that word information exists it’s time taken process. Instead of reading the each page of book to get that particular word information if I check the index of book (Glossary) it is much quicker for us to get the pages which contains the information with Index Page

This same logic applies for retrieving data from a database table. Without an SQL Index, the database system reads through the entire table to locate the desired information. If we set the proper index in place, the database system first go through the index to find out where to retrieve the data, and then go to that location directly to get the needed data. This is much faster due to the SQL Index. Creating and removing indexes on table will not show any affect on application because indexes operate behind the scenes.

For Example:

create table tbEmp(SrNo int,Name varchar(50),Salary int)

insert into tbEmp values(1,'Kafil Alam',90000)

insert into tbEmp values(3,'Shivak Sharma',15000)

insert into tbEmp values(5,'Anupam',12000)

select * from tbEmp

Create Index with Sql Query………………………….

-- query for create index

create index tb_name on tbEmp(Name asc)

-- To Check index

sp_helpindex tbEmp

--To drop index with table  name

drop index tbEmp.tb_name

select * from tbEmp

Note: By Default index is non-clustered index

Create Index with Sql Wizard ………………………….

Type of Indexes

1. Clustered Index
2. Non Clustered Index
3. Unique
4. Filtered
5. XML
6. Full text
7. Spatial
8. Column Store
9. Index with includes columns
10. Index with computed columns

What is Clustered Index A Clustered Index determine the physical order of data in a table for this reason a table can have only one clustered index

For Example:

create table tblstudent( RollNo int ,Name varchar(50),Gender varchar(50),Course varchar(50))

execute sp_helpindex tblstudent

Error is The object 'tblstudent' does not have any indexes, or you do not have permissions.

insert into tblstudent values (3,'Kush Tiwari','Male','Mca')

insert into tblstudent values (1,'Ravi Singh','Male','MTech')

insert into tblstudent values (5,'Himank Singh','Male','Bca')

select * from tblstudent

Result:

Note: when RollNo Column is maked as primary key ,P K Constraint create clustered index automatically if no clustered index already exists  the table

drop table tblstudent

create table tblstudent( RollNo int primary key ,Name varchar(50),Gender varchar(50),Course varchar(50))

insert into tblstudent values (3,'Kush Tiwari','Male','Mca')

insert into tblstudent values (1,'Ravi Singh','Male','MTech')

insert into tblstudent values (5,'Himank Singh','Male','Bca')

execute sp_helpindex tblstudent

select * from tblstudent

Result

Create a composite clustered index on the table Name and Gender columns...............

create clustered Index Ix_tblStudent_NameGender on tblstudent(Name asc,Gender desc)

when execute this query you can found this type error…….

Error Message   Msg 1902, Level 16, State 3, Line 1

Cannot create more than one clustered index on table 'tblstudent'. Drop the existing clustered index 'PK__tblstude__7886D5A024927208' before creating another.

Means you can not create more than one clustered index on table tbstudent

Then drop index with this line

drop index tblstudent.PK__tblstude__7886D5A01FCDBCEB

or  you can directly from sql  wizard …………………..

then again  executed this  query

create clustered Index Ix_tblStudent_NameGender on tblstudent(Name asc,Gender desc)

select * from tblstudent

What  is  Non-Clustered Index in Sql Server  A non clustered index is anologouse to an index in a textbox .The index will have pointers to store loction of data

Since the non clustered index is stored  separetly from actual data a table can have more than one non clustered index just now a book can have any index by chapters at the beginning and another index by common terms at the end

For Example

create nonclustered index Ix_tblStudent_Course on tblstudent(Course)

Note: In the index itself ,the data is stored in an asending or descending order of the index key ,which doesnot in any way influence the store of datain the table

create nonclustered index Ix_tblStudent_Course on tblstudent(Course desc)

Result

Difference  between Clustered and Non-Clustered Index in Sql Server

Only One clustered Index per table,where as you can have more than one non clustered index

Clustered Index is faster than non clustered index,because ,the clusered index has to refer back to table if selected column is  not present

Culstered Index determine the store order row in the table,and hence does not requeired additional disk space but where as a non Clustered index is stored separately from the table,additional storage space is requried

·