SQL Server Indexing and Composite Keys

Question

Given the following:

-- This table will have roughly 14 million records
CREATE TABLE IdMappings 
(
    Id int IDENTITY(1,1) NOT NULL,
    OldId int NOT NULL,
    NewId int NOT NULL,
    RecordType varchar(80) NOT NULL, -- 15 distinct values, will never increase
    Processed bit NOT NULL DEFAULT 0,

    CONSTRAINT pk_IdMappings 
        PRIMARY KEY CLUSTERED (Id ASC)
)

CREATE UNIQUE INDEX ux_IdMappings_OldId ON IdMappings (OldId);
CREATE UNIQUE INDEX ux_IdMappings_NewId ON IdMappings (NewId);

and this is the most common query run against the table:

WHILE @firstBatchId <= @maxBatchId
BEGIN
    -- the result of this is used to insert into another table:
    SELECT
        NewId, -- and lots of non-indexed columns from SOME_TABLE
    FROM
        IdMappings map
    INNER JOIN
        SOME_TABLE foo ON foo.Id = map.OldId
    WHERE
        map.Id BETWEEN @firstBatchId AND @lastBatchId
        AND map.RecordType = @someRecordType
        AND map.Processed = 0

    -- We only really need this in case the user kills the binary or SQL Server service:
    UPDATE IdMappings 
    SET Processed = 1
    WHERE map.Id BETWEEN @firstBatchId AND @lastBatchId
      AND map.RecordType = @someRecordType
            
    SET @firstBatchId += 4999
    SET @lastBatchId += 4999
END

What are the best indices to add? I figure Processed isn't worth indexing since it only has 2 values. Is it worth indexing RecordType since there are only about 15 distinct values? How many distinct values will a column likely have before we consider indexing it?

Is there any advantage in a composite key if some of the fields are in the WHERE and some are in a JOIN's ON condition? For example:

CREATE INDEX ix_IdMappings_RecordType_OldId 
    ON IdMappings (RecordType, OldId)

... if I wanted both these fields indexed (I'm not saying I do), does this composite key gain any advantage since both columns don't appear together in the same WHERE or same ON?

Insert time into IdMappings isn't really an issue. After we insert all records into the table, we don't need to do so again for months if ever.

SQL Server Indexing and Composite Keys

0 Answers