Skip to content

Add a Covered Index to tables to get rid of Bookmark Lookups

Add a Covered Index to tables to get rid of “bookmark lookups.” The idea of bookmarks: SQL Server uses Bookmarks when you use columns in your query that are not non-clustered/clustered indexes. So if we have something like this:

SELECT  AreaID,
        ContentID,
        ApprovalCodeID,
        isSubmissionPoint

FROM    ContentAreas

WHERE   AreaID = 31
        AND ContentID > 30000
        AND ModifierID > 65000

And let’s say that AreaID is a clustered index and ContentID is a non-clustered index. If ModifierID is not an index of any kind, it would function as a “bookmark lookup” in this query. To not use a “bookmark lookup,” use a Covered Index, which clumping the Modifier column with ContentID and turning both into a Covered Index (a kind of composite index).

Categories

SQL Server

Dan View All

Blog owner.

%d bloggers like this: