Indexes are database objects can be created on one or more columns of a table(Max 16 columns).
Database Index is like book index that make easy to find out specific record. Creating index on table increase performance to fetch data but add some overhead for DML(Insert, Update, Delete) operations.
Clustered Index - When we create primary key database read the column values and creates Clustered Index for it. It can be only one for one table.
Select * from Customer where customerid = 103
Select * from Customer where customerid = 100
Without indexing these queries will return the result after 100+ comparisons.
With indexing, execution of first query will return value at first comparison. Execution of second query will return the value at the third comparison. See below example for second query:
For query no. 2
Compare 100 vs 103 : Move to left node
Compare 100 vs 101 : Move to left node
Compare 100 vs 100 : Matched, return the record
Non Clustered index - Its useful to create on columns those have repetitive values, like those have thousands records but unique are 10-20.
Database Index is like book index that make easy to find out specific record. Creating index on table increase performance to fetch data but add some overhead for DML(Insert, Update, Delete) operations.
Clustered Index - When we create primary key database read the column values and creates Clustered Index for it. It can be only one for one table.
Select * from Customer where customerid = 103
Select * from Customer where customerid = 100
Without indexing these queries will return the result after 100+ comparisons.
With indexing, execution of first query will return value at first comparison. Execution of second query will return the value at the third comparison. See below example for second query:
For query no. 2
Compare 100 vs 103 : Move to left node
Compare 100 vs 101 : Move to left node
Compare 100 vs 100 : Matched, return the record
Non Clustered index - Its useful to create on columns those have repetitive values, like those have thousands records but unique are 10-20.
No comments:
Post a Comment