deepakblog

SQL PERFORMANCE TUINING :-

1) CLUSTER AND NON-CLUSTER INDEX :-

both can be used for searching purpose.

CLUSTER INDEX :

It can be used for searching purpose

Any column can have Primary key or index then it will be a cluster index.

there will be only 1 cluster index in a table.

eg:-

select * from Appoinmnet where id=51;

1)soo cluster index 1st create B-tree n divide index value 1-50 and 51-100

(balance tree it can balancely divide the records agar 100 he to 50 , 50 me divide krega)

2)then index can check condition is 50<51 not so skip 1-50 and search on 51-100 index

3) it gate 51 index then it index have leaf node which provide him value

NON-CLUSTER INDEX :-

it can be used for searching purpose but it leaf node cannot have value it need to take the help of cluster index Leaf node for value

eg:-

select * from Appoinmnet where id=51;

1)soo non-cluster index 1st create B-tree n divide index value 1-50 and 51-100

(balance tree it can balancely divide the records agar 100 he to 50 , 50 me divide krega)

2)then index can check condition is 50<51 not so skip 1-50 and search on 51-100 index

3) it gate 51 index then it take the help of cluster index Leaf node to get the value

🙏ON WHICH COLUMN THE CLUSTER INDEX CAN USED ?

-> CLUSTER INDEX CAN USED FOR PRIMARY KEY AND INDEX AND THERE IS ONLY 1 CLUSTER INDEX IN TABLE

🙏ON WHICH COLUMN THE NON-CLUSTER INDEX CAN USED ?

-> FOR FOREIGN KEY ,PHONE NO SEARCHING AND THERE WILL BE 999 NON-CLUSTER INDEX IN A TABLE

🙏 BOTH CLUSTER AND NON-CLUSTER CAN CREATE B-TREE

INDEX-SCAN CAN MAKE SEARCH FAST BUT NOT INSERT ,UPDATE ,DELETE

DUE TO PAGE SPLIT