newbie2 (9/24/2012)
AND RTRIM(LTRIM(SD.Code ))<>'';
The RTRIM and LTRIM are unnecessary here. SQL ignores trailing spaces when checking string equality, hence '' = ' ', true no matter how many spaces you have.
That predicate can be reduced to AND SD.Code != ''. That != will also eliminate nulls, so you can remove the SD.Code IS NOT NULL as well.
Neither of the nonclustered indexes that you've created look optimal. Rather consider
Table1: Index key (Code, Batch, Flag, Num) Include (Type)
Table2: Index key (Flag, Batch, Num, Date1) Include (ID)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability