SqlServer Indexes Cont..

In this post,I will be extending  the discussion on indexes from my previous post,as we were discussing on non clustered indexes ,I will be covering a specific type of non clustered index called covering index.Before going in detail ,let me explain    what happens when   we   execute a query which has got non clustered index  with  clustered index optional.

Take an example table Product  with columns as Pid  Pname PCategory PsubCategory ,assume we got a clustered  index on Pid  and non clustered index on Pname ,Lets   execute a query like Select  Pcategory,PSubCategory  from Product.

We got a  Non clustered index on Pcategory but not on PsubCategory so what sqlserver optimizer  does

when  a column  in the select query is not included in an index is  a book mark look up ,during this book mark look up it makes a look up to a  clustered index in this case or a row id if we haven't got clustered index and fetches relative rows  from the table,clearly as you can observe  there  is an extra step  for this and this degrades performance,so in this case if  we include a non clustered index on the other column PsubCategory also ,than we can avoid the book mark look as we got both the columns of the select query in the form of indexes and fetched directly  without needing a lookup to a clustered index or row location .This naturally improves performance .

I  recommend to  have look at  graphical execution plans in Management studio which will give you a quick overview of the Various metrics associated with the cost of the queries,but remember spend as much time as you can on indexes and the way optimizer executes in sqlserver