When Indexes are used for retriving the inforamtion from table in sql server ?

When Indexes are used for retriving the inforamtion from table in sql server

-> Sql server will use indexes provided the statement is a Select or Update or Delete also

-> Before Executing the above statement sql server will choose for the best query plane to execute the statements,which can be either fullpage scan or a indexer scan.

-> Sql server uses index for executing a statement provided the statement contains a where clause in it and the column used in the where clause is the column on which the index is present.


  Select * from Employee                    --Index is not used

  Select * from Employee Where DeptNo=20     --Index is not used

  Select * from Employee Where Sal=5000       --Index is used

   Select * from Employee Where Mgr=1001      --Index is used

   Select * from Employee Where EmpNO=1001    --Index is used


-> Indexes are used by SQl Server provided a query contains a order by query clause in it aslo provided the cloumn used in the order by clause is the column on which the index is present.
  Select * from Employee order by DeptNo      --Index is not used

   Select * from Employee order by Sal         --Index is used

When should we create indexes on a table
-----------------------------------------

 If we want to create an index on a table.we need to create indexes on those columns which are frequently used in the where condition or order by clause,its not suggest to create an index on the cloumn's that are not frequently used for searching or storing the data because more number of indexes on a table will aslo degrade the performance of the server because every DML operation.we pefrom on the table should have the sub sequent entery's in index pages associated with the table.
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments