That explains the bad performance pretty much...

You're using a table variable just like I did for my demo version. :blush: My fault. I'm sorry.

A table variable is a bad option to use since it's usually populated without proper indexing causing a table scan instead of a index seek. Furthermore, there are no statistics available for the query optimizer to come up with the best query plan. Instead QO will assume that the table varialbe will have only one row.

I thought you would replace the table variable with your original table, but I didn't mention it.

Now that I see you're using a computed column as a replacement of ckid, I'd recommend you make the following changes (+ change the table name in the CTE).

CREATE TABLE #tmpChecks

(

OrgCheck char(15) PRIMARY KEY CLUSTERED,

OrgId smallint,

CheckNum int,

Status char(1),

AmtCheck money,

CheckDate char(10),

RegisterId char(12)

)

INSERT INTO #tmpChecks

SELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,

OrgId, PrintedCheckNum as CheckNum, Status,

AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,

CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId

from MyTable WITH (NOLOCK)

WHERE

ISNUMERIC(PrintedCheckNum) = 1

order by OrgCheck

I'd be curious to know the timing of this code compared to the previous solution...

Again, I'm sorry...