None of the existing NC indexes are covering.

Create the new index. Change the order of the key columns if necessary.

Then monitor index usage, the cost of maintaining the...

It won't matter if OP creates the index I suggested. Since it's covering,...

If you find the time to create the index I suggested, I'd be interested in seeing the execution plan 😉

ananda.murugesan (7/15/2013)

---

I modified Query as per your suggestion and fetching result 00 seconds..But NC clustred index seek 50% and .. what could be issues?, Need to change index column? and...

Can you post the actual plan?

Joins in the WHERE clause are...

Drop the table variables altogether:

SELECT

a.MemberId,

a.SortOrder ,

a.Status ,

a.ReasonCD ,

a.Active,

r.Rid,

r.RType,

r.Sensitive,

r.Weight,

r.ROI,

r.Program,

r.Mgmt_Ranking,

r.Significant,

(select top 1 Name from library WITH (NoLock)

where rID = r.ruleid) as ruleName,

min(b.analysisasofdate) initiallyIdentified,

max(b.analysisasofdate) mostRecentlyIdentified,

a.category,

v_cnt.ResponseCategoryCount,...

And use temporary tables with columns which match the joins in the query:

inner join @alerts a on a.ruleid = r.ruleid

and a.alertbatchid =...

Hi Kath, welcome to ssc!

-- unless you are checking for NULL,

-- referencing a column from an OUTER-joined table

-- will turn the join into an INNER join

-- Here's the...

You're welcome! I learn something...

kapil_kk (7/12/2013)

---

Chris, I am not clear what you did....

Will this query be work in this manner..

If a voucher is 'Active at Ho' or its status is 'Dispatched' it is correct...

Here are queries 3 and 4 condensed in the same way;

SELECT gv.VoucherBookletNo

FROM GV_Voucher v

INNER JOIN GV_VoucherStatus gvs

ON v.VoucherStatusId = gvs.VoucherStatusId

WHERE v.VoucherNo = @Lastvoucher

AND (

(gvs.VoucherStatus =...

You can replace the first two queries with one query:

SELECT gv.VoucherNo

FROM GV_Voucher As gv

INNER JOIN GV_VoucherStatus gvs

ON gv.VoucherStatusId = gvs.VoucherStatusId

AND gvs.VoucherStatus = 'Active at HO'

WHERE gv.VoucherNo...

Rearrange the first two queries like so:

IF EXISTS

(

SELECT gv.VoucherNo

FROM GV_Voucher As gv

INNER JOIN GV_VoucherStatus gvs

ON gv.VoucherStatusId = gvs.VoucherStatusId

AND gvs.VoucherStatus = 'Active at HO'

INNER JOIN GV_ReceivedOffice...

asifejaz (7/12/2013)

---

the resolution didnt solve it.

it decrement the first row successfully. when moving to second row instead of decrementing it to one it decrements it to two , then to...

DROP table tbl

CREATE TABLE tbl

(ID int identity, Voucherno varchar(10), numstatus varchar(10)

)

INSERT INTO tbl VALUES

('V0001','Active'),

('V0002','Active'),

('V0003','Active'),

('V0004','InActive'),

('V0005','Active')

DECLARE @firstno varchar(10) = 'V0001',

@scndno varchar(10)...