Sql Server: index part 4

a.    What is unique index?
b.    What are the advantages and disadvantages of index?

Unique index

•            By default, Primary key constraint creates a unique clustered index on a table, whereas unique constraint creates unique non clustered index.

•       When the index is dropped, primary key constraint is also dropped.

•       Unique is a property of an index (for both clustered and non clustered index.

•      There is no difference between unique constraint and unique index. When unique constraint is added, then index also gets created behind the scene.

•       If a UNIQUE constraint is added to a column that has duplicated values, the Database Engine returns an error and does not add the constraint.

Advantages of indexes

• Indexes provide faster data access during specific data fetch and retrieval form huge data tables. Below are the scenarios in which it works well.

• Update and delete commands also work well when specific records or range of records are searched for modification or deletion. For example, if an index is created on salary column of employee table, and one has to modify salary for the employees who are having salary 10000, so it will be easier and faster to look for particular records and apply delete and update operations.

• In case of sorting also, when we ask for a sorted dataset, the database will try to find an index and avoid sorting the results during execution of the query. 

• In case of grouping also, say one has to count the number of employee as per annual compensation (salary column), and the index is created on salary. Since matching salary appears in consecutive index entries, then the database will be able to count the number of employee at each salary quickly. While if index was not there on salary then it will first sort the results.

Disadvantage

•  Additional disk space is required in case of non-clustered index.

• Insert, update and delete can become slow. As it has to locate the records then it takes time in case of huge data and too many indexes are there, because on each DML operation indexes will also be updated.

• If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. 

for more questions and answers on indexes
Previous      Next

Understanding on Indexes