this is the query :

SELECT [DesignChanges].* FROM [Checklists].dbo.[DesignChanges]

WHERE

(

Charindex('jyo',[Checklists].dbo.[DesignChanges].[AllotedTo])>0 AND

(

([Checklists].dbo.[DesignChanges].[WorkImplemented] Is Null OR [Checklists].dbo.[DesignChanges].[WorkImplemented] = '' )

OR

(

([Checklists].dbo.[DesignChanges].[WorkImplemented] Is Not Null AND [Checklists].dbo.[DesignChanges].[WorkImplemented]<>'')

AND([Checklists].dbo.[DesignChanges].[DocumentReqd] <> 'NA/' or [Checklists].dbo.[DesignChanges].[DocumentReqd] is null )

AND([Checklists].dbo.[DesignChanges].[DocumentDoneBy] Is Null OR [Checklists].dbo.[DesignChanges].[DocumentDoneBy] = '')

)

)

)

AND ([Checklists].dbo.[DesignChanges].[Pending] Is Null OR [Checklists].dbo.[DesignChanges].[Pending] = '' )

AND Charindex('Queries',[Checklists].dbo.[DesignChanges].[TypeOfWork])>0

AND (Charindex('~!',[Checklists].dbo.[DesignChanges].[F6Row])=0 or [Checklists].dbo.[DesignChanges].[F6Row] Is NULL )

the table is:

Sl_no int

Date date

Operation nvarchar(MAX)

AllotedTo varchar(50)

UserApproval xml

[By] varchar(50)

DbName varchar(255)

TableName xml

Remarks varchar(5000)

WorkImportance int

F6Row varchar(200)

F6Givenby nvarchar(MAX)

F8Row varchar(1000)

F8Givenby varchar(1000)

ListCol nvarchar(255)

pro_remarks nvarchar(MAX)

WorkImplemented varchar(50)

WorkTime xml

DetailedPlan varchar(255)

UserChecked xml

TestedBy xml

Priority tinyint

TasklistId varchar(50)

Discussed xml

Permitted_By varchar(50)

PrelimPlan varchar(255)

[User] varchar(255)

ApprovalReady varchar(50)

Sup_Approval xml

ManagerApproval xml

Pending varchar(50)

IncReady varchar(50)

IncReadyDate date

IncApproval varchar(50)

[Inc By] varchar(50)

[Inc Date] date

DocumentReqd varchar(255)

AfterInc_Check xml

TBFill_To varchar(255)

Documents varchar(255)

DocumentDoneBy varchar(50)

DocumentIncBy varchar(50)

SupChecked xml

ManagerChecked xml

JobTitle xml

DoneBy_PreTest xml

TestRecId varchar(50)

TypeOfWork varchar(50)

Bgd_test varchar(MAX)

bgd_Ph varchar(MAX)

i have use the filtered index

:

CREATE NONCLUSTERED INDEX [Checklists]

ON DesignChanges(Pending)

WHERE (Pending IS NULL AND Pending =' ')

GO