Steven Willis (9/25/2012)
Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.
SELECT
[Desc]
,[Num]
,[Batch]
,[CodeId]
,[Type]
,[Id]
FROM
(
SELECT DISTINCT
CD.[Desc] AS [Desc]
,SD.[Num] AS [Num]
,SD.[Batch] AS [Batch]
,CD.[CodeId] AS [CodeId]
,SD.[Type] AS [Type]
,CH.[Id] AS [Id]
,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]
FROM
Table1 SD
JOIN
Table2 CH
ON CH.Num = SD.Num
AND CH.Batch = @Batch
AND CH.Flag = 0
AND SD.Batch = @Batch
AND SD.Flag = 0
JOIN
Table3 M
ON M.Code = SD.Code
AND (Map = 1
OR Map = @Map)
AND CH.Date1 BETWEEN M.Date2 AND M.Date3
JOIN
Table4 CD
ON M.CodeId = CD.CodeId
) AS Result
WHERE
[Code] <> '0'
There's nothing wrong with your reasoning, Steven - it looks like a winner. However, I strongly suspect that the execution plan of your query would be identical to this:
SELECT DISTINCT
CD.[Desc] AS [Desc]
,SD.[Num] AS [Num]
,SD.[Batch] AS [Batch]
,CD.[CodeId] AS [CodeId]
,SD.[Type] AS [Type]
,CH.[Id] AS [Id]
,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code]
FROM
Table1 SD
JOIN
Table2 CH
ON CH.Num = SD.Num
AND CH.Batch = @Batch
AND CH.Flag = 0
AND SD.Batch = @Batch
AND SD.Flag = 0
JOIN
Table3 M
ON M.Code = SD.Code
AND (Map = 1
OR Map = @Map)
AND CH.Date1 BETWEEN M.Date2 AND M.Date3
JOIN
Table4 CD
ON M.CodeId = CD.CodeId
WHERE
ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') <> '0'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden