Clustered and Non-Clustered Index. Session 3

Clustered and Non-Clustered Index.

Need to first understand why we need indexing and what are the disadvantages, if we don't use indexing.

 Performance will get decreased
 We will not get our data quickly

To sort out this issue we have indexes. There are two types of indexing in SQL.
1. Clustered index
2. Non-clustered index

Clustered Index–
 Defining a column as a primary key makes that column the Clustered Index of that table.
 We can create only one clustered index on a table. To make any other column, the clustered index, first we have to remove the existing one.
 We can create an index containing more than one column is called composite index. Possible to have more than one column for that index created.

Non-Clustered Index
• Non-clustered index stores the data at one location and indexes at another location. Index contains pointers to the location of that data.
• Non-clustered index requires additional disk space. Because non clustered indexes are stored separately.
• Non-clustered index is slower. Because it has to refer back to base table.
• A single table can have many non-clustered indexes as an index in the non-clustered index is stored in different places.
----------------------------------------------------------------------------------------------------------------------
create table customer1
(
id int,
name varchar(30),
salary int
)
----------------------------------------------------------------------------------------------------------------
insert into customer1 values(1, 'mahesh',1000)
insert into customer1 values(5, 'ramesh',2000)
insert into customer1 values(2, 'rahul',3000)
insert into customer1 values(3, 'bupesh',4000)
insert into customer1 values(4, 'sudesh',5000)
insert into customer1 values(5, 'sudesh',6000)
------------------------------------------------------------------------------------------------------------
create table customer2
(
id int primary key,
name varchar(30),
salary int
)
---------------------------------------------------------------------------------------------------------
insert into customer2 values(1, 'mahesh',1000)
insert into customer2 values(5, 'ramesh',2000)
insert into customer2 values(2, 'suresh',3000)
insert into customer2 values(3, 'bupesh',4000)
insert into customer2 values(4, 'sudesh',5000)
--------------------------------------------------------------------------------------------------
create clustered index clusindex on customer2(name asc,salary asc)
sp_helpindex customer1
create clustered index clusindex1 on customer2(salary desc )
create nonclustered index nonclusindex on customer2(id asc)
create nonclustered index nonclusindex2 on customer2(name asc)
create nonclustered index nonclusindex3 on customer2(salary asc)

[yogeshpandita]

Finally, most ever beloved and Master class Teacher is here.... What a GOD gifted teaching skills and Method....never ever seen in my life....

[nishapathania]

Well explained sir. I had seen so many videos on same, but first time had understood the concept clearly..keep posting and thanks...

[sqlserverdba]

Your videos so good I request you to upload all previous videos

[sivaram]

Please do the video on query optimization and performance tuning

[Contentkashyapa]

sir your previous videos were still more videos but now its only fewer please upload them ASAP.

[jacksparrow]

bro atleast tag the person from where you taken the content .