indexes

Index is a database object, which can be created on one or more columns . When creating the index will read the column(s)
and forms a relevant data structure to minimize the number of data comparisons. The index will improve the performance of data retrieval and adds some
overhead on data modification such as create, delete and modify.
So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations.


indexes are two types
1.clustered index
2.Nonclustered index.

1.clustered index:
------------------


-->A clustered index  is an index that srortes the data rows in the table based on their key values.

-->onley one clustered index can be created per table


in the clustered index ,data is stored at the leaf level of the B-tree.


sql server performs the following steps when it uses a clustered index to search for a value

1.SQL server obtains the address of the root page from the sysindexes table,which is a system table containing the details of all the indexes in the database


2.the serch value is compared with the key values on the root page .

3.the page with the highest key values less then or equal to serch value is found

4.the page pointer is followed to  the next lower level in the index .

5. steps 3 and 4 are repeated until the page is reached .

6.the rows of the data are searched on the datapage untill the serch value is found .if the serch value is not found on the data page ,no rows are returned by
the query


2.Nonclustered index.
----------------------
Similar to the clustered index,a Nonclustered index also contains the index key values and the row locators that point to the
storage location of the data in the table .however ,in a nonclustered index,the physical order of the rows is not the same as the
index order .



non clustered indexes are created on columns used in joins and where clause .
the sql server creates non clustered indexes by default when the CREATE  INDEX command is given .
there can be as maney as 999 nonclustered indexes per table.


the data in a non clustered index is present in a random order ,but the logical ordering is specified by the index

EX:


CREATE DATABASE VENKAT


USE VENKAT
GO

---CREATE TABLE


CREATE TABLE Student
(
          StudId smallint,
StudName varchar(50),
Class tinyint
)

 -----TABLE 2-----
CREATE TABLE TotalMarks
(
StudentId smallint,
TotalMarks smallint
);


 -----creating the indexes----

CREATE CLUSTERED INDEX CL_IX_Student
ON Student(StudId)
WITH FILLFACTOR = 10

 ---DROP INDEX----


DROP INDEX CL_IX_Student ON Student


---INSERTING THE VALUES ----


INSERT INTO Student
(
StudId ,
   StudName ,
   Class
   )
   VALUES(1,'SRI',2),
(2,'MOHAN',3),
(3,'MANU',2),
(4,'HARI',4),
(5,'KRISHNA',3),
(6,'MANU',3),
(7,'KIRAN',3),
(8,'VENKAT',4),
(9,'AKHEEL',5),
(10,'RAJENDER',5),
(11,'KISHOR',6),
(12,'KRISHNA',4)

---TO RETRIVE THE ALL THE REC FROM TABLE----

   SELECT * FROM STUDENT