• Sorry to spam, just to add, the other 2 columns, on which its not doing a key look up (Message, Datetimestamp) are not part of the clustered index. Here's the entire table + index definition:

    CREATE TABLE [tblBIUsers](

    [BIUserID] [bigint] IDENTITY(1,1) NOT NULL,

    [LoginName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Password] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [FirstName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [MiddleName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [LastName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblBIUsers_Gender] DEFAULT ('X'),

    [Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Student] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [OrganisationID] [bigint] NULL,

    [TestBatchID] [uniqueidentifier] NOT NULL,

    [TestPartSeqNumber] [int] NOT NULL,

    [TestBatchName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [RuTestBatchID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [RuTestBatchStatus] [tinyint] NULL,

    [UserStatus] [tinyint] NULL,

    [Message] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [CourseID] [bigint] NULL,

    [IsDeleted] [bit] NOT NULL CONSTRAINT [DF_tblBIUsers_IsDeleted] DEFAULT ((0)),

    [DateTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_tblBIUsers_DateTimeStamp] DEFAULT (getdate()),

    [DateTimeStampIns] [datetime] NOT NULL CONSTRAINT [DF_tblBIUsers_DateTimeStampIns] DEFAULT (getdate()),

    [UploadedUserID] [bigint] NULL,

    [UploadedDate] [datetime] Not NULL CONSTRAINT [DF_tblBIUsers_UploadedDate] DEFAULT (getutcdate()),

    [BusinessRuleSet] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    CONSTRAINT [PK_tblBIUsers] PRIMARY KEY NONCLUSTERED

    (

    [BIUserID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'CIX_tblBIUsers')

    CREATE CLUSTERED INDEX [CIX_tblBIUsers] ON [tblBIUsers]

    (

    [TestBatchID] ASC,

    [TestPartSeqNumber] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'IX_tblBIUsersRuTestTestMap')

    CREATE NONCLUSTERED INDEX [IX_tblBIUsersRuTestTestMap] ON [tblBIUsers]

    (

    [RuTestBatchID] ASC,

    [TestPartSeqNumber] ASC

    )

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'IX_tblBIUsersUplDtPwdIsDel')

    CREATE NONCLUSTERED INDEX [IX_tblBIUsersUplDtPwdIsDel] ON [tblBIUsers]

    (

    [UploadedDate] ASC,

    [Password] ASC,

    [IsDeleted] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'IX_tblBIUsers_OCU')

    CREATE NONCLUSTERED INDEX [IX_tblBIUsers_OCU] ON [tblBIUsers]

    (

    [OrganisationID] ASC,

    [CourseID] ASC,

    [UploadedUserID] ASC

    )

    INCLUDE ( [UploadedDate],

    [RuTestBatchID],

    [RuTestBatchStatus],

    [UserStatus],

    [IsDeleted]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO