santosh-asp tutorial

Advantages and Disadvantages of Index

Anvantages and Disadvantages of Index:-The use of Index in SQL SERVER has many advantages and also some disadvantages. For this we are going to create a table as following.

  

CREATE TABLE tblStudentDetail
(
StuId int primary key,
Name varchar(100),
Branch varchar(30),
Fee int,Email varchar(100),
Address varchar(200)
)


----------Insert some records in tblStudentDetail table----------
INSERT INTO tblStudentDetail VALUES(1001,'Santosh','IT',1200,'s@gmail.com','A-34')
INSERT INTO tblStudentDetail VALUES(1002,'Suraj','CSE',1500,'s@gmail.com','E-145')
INSERT INTO tblStudentDetail VALUES(1003,'Abhinav','ME',2000,'s@gmail.com','P-964')
INSERT INTO tblStudentDetail VALUES(1004,'Pooja','CSE',3000,'s@gmail.com','K-84')
INSERT INTO tblStudentDetail VALUES(1005,'Arun','MCA',5200,'s@gmail.com','L-35')
INSERT INTO tblStudentDetail VALUES(1006,'Anjanee','MCA',5200,'s@gmail.com','A-33')

Create a Non-Clustered Index on Salary Column:-

Create NonClustered Index IX_tblStudentDetail_Fee On tblStudentDetail (Fee Asc)

Advantages:-

Select Statement:- The following select query benefits from the index on the Fee column, because the Fees are sorted in ascending order in the index. From the index, it's easy to identify the records where fee is between 1500 and 5000, and using the row address the corresponding records from the table can be fetched quickly. We can also display records by "ORDER BY" and "ORDER BY Fee DESC".
```
  

SELECT Fee as Student_Fee FROM tblStudentDetail WHERE Fee>1500 and Fee<5000

-----------Display Record ORDER BY------------
SELECT * FROM tblStudentDetail ORDER BY Fee

-----------Display Record ORDER BY Fee DESC------------
SELECT * FROM tblStudentDetail ORDER BY Fee DESC
```
Update and Delete Statements:- The following UPDATE and DELETE statements can also benefit from the index. To update or delete a row, SQL server needs to first find that row, and the index can help in searching and finding that specific row quickly.
```
  

UPDATE tblStudentDetail SET Fee=4500 WHERE Fee=3000

DELETE FROM tblStudentDetail WHERE Fee=1500
```
GROUP BY queries:-To group the Students with the same Fee, the query engine, can use the index on Fee column, to retrieve the already sorted fees. Since matching fees are present in consecutive index entries, it is to count the total number of Students at each Fee quickly.
```
  

Select Fee, COUNT(Fee) as Total from tblStudentDetail Group By Fee
```

Diadvantages of Indexes:-

Additional Disk Space:- Clustered Index does not, require any additional storage. Every Non-Clustered index requires additional space as it is stored separately from the table.The amount of space required will depend on the size of the table, and the number and types of columns used in the index.
Insert Update and Delete statements can become slow:- When DML (Data Manipulation Language) statements (INSERT, UPDATE, DELETE) modifies data in a table, the data in all the indexes also needs to be updated. Indexes can help, to search and locate the rows, that we want to delete, but too many indexes to update can actually hurt the performance of data modifications.
What is a covering query?
If all the columns that you have requested in the SELECT clause of query, are present in the index, then there is no need to lookup in the table again. The requested columns data can simply be returned from the index.