Key Lookup isn't applied by default? I'm trying to learn about covering indexes. In the Northwind database, I select from the table Categories:

As you can see the table has a non-clustered index on the column CategoryName.
This SQL query:
select CategoryName  from Categories where Categories.CategoryName like 'Beverages'

returns an execution plan with an index seek:

However, this:
select CategoryName ,Description from Categories where Categories.CategoryName like 'Beverages'

returns this execution plan with an index scan using the primary key index, which isn't expected:

I can find the expected behaviour only when I force the query with the non-clustered index:
 select CategoryName ,Description from Categories
    with(index(CategoryName))
 where Categories.CategoryName like 'Beverages'


What is the problem?
There is no Description column in your index but there is in your query. So, SQL Server has to get this column. There are two options for doing that:

non-clustered index seek + key lookup
clustered index scan

If your statistics make SQL Server think it will read a lot of data, SQL Server makes a choice between the non-clustered index seek + key lookup or clustered index scan, this called tipping point.
Because, if SQL Server has to read huge data, the clustered index scan can be more efficient rather than non-clustered index seek + key lookup.
There is a good blog post here.