Let say I have non clustered index is created with include columns If we query select col2, col3 from table1 where col1=value1
Index is created on col1 with include col2,col3,col4.
In this case, Will my non clustered index will be used, and it will use include column from leaf node? or it will do another look up for col2, and col3? as include has more columns then selected columns?
CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3,Col4)
My select is:
select col2,col3 from mytable where col1=value1
Yes, your query should use the index you defined earlier. I say "should," because as always there are caveats when this might not be the case, but in general I would expect SQL Server to use the index. That you cover in the INCLUDE clause more columns than your select is demanding doesn't really change much. It only means that the database won't use the Col4 information. But everything else, e.g. traversing the B-tree, should behave exactly the same as if you had only covered exactly the columns your select is requesting.
Back to the "should" part. If your table were really small, say only a few dozen records or less, then SQL Server might not choose to use any index, instead going for a full table scan. There is an implicit up front price to pay for using an index, and for a very small table, it might not be worth the price. There are probably other edge cases out there as well.