I am trying to understand why does this query a key lookup, even if I added all columns i am selecting in the index?
Here's the query
SELECT TOP 1
ART.Id,
ART.Title,
ART.InitialTitle,
ART.Summary,
ART.IsCategoryFirst,
ART.RootCategoryId,
CAT.Name,
ART.OldFacebookCommentsUrl,
ICO.CssClass,
ART.TopicName,
ART.MainArticlePhoto,
ART.FrontPagePhoto,
ART.PublishDate
FROM Articles ART WITH (NOLOCK)
INNER JOIN Categories CAT WITH (NOLOCK) ON CAT.Id = ART.RootCategoryId
LEFT JOIN ArticleIcons ICO WITH (NOLOCK) ON ICO.Id = ART.IconId
WHERE ART.RootCategoryId = 3
AND ART.PublishDate < GETDATE()
AND ART.Active = 1
AND IsCategoryFirst = 1
Here is the index which should cover it
CREATE NONCLUSTERED INDEX [SmallArticleResponse] ON [dbo].[Articles]
(
[RootCategoryId] ASC,
[Active] ASC,
[PublishDate] DESC,
[Coefficient] DESC
)
INCLUDE ( [Id],
[Title],
[InitialTitle],
[OldFacebookCommentsUrl],
[Summary],
[IconId],
[TopicName],
[FrontPagePhoto],
[MainArticlePhoto],
[FacebookPhoto],
[IsCategoryFirst]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Because IsCategoryFirst column isn't in the key, most likely. And/or because there is a separate (single column?) index on IsCategoryFirst and it's using that instead.