Clustered and Non-Clustered Index. Session 3 скачать в хорошем качестве

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) drop index customer2.clusindex 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)