How to optimioze the Case statement for a column. Please help

1) CASE is an expression, not a statement.

2) You create a table ( CodeCheck, NDS ), then you can JOIN it with your table. This replaces the WHERE condition as well as the CASE expression.

3) Why those all those TRIM calls? I would think that non of those columns needs that. But when it is necessary, then why not updating the table before running your tests?

Thank you EchoLiu, Can you please share example using with or as you mentioned?please

Hi @aswaniD-8992n

Welcome to the Microsoft TSQL Q&A Forum!

If it is to improve performance, you can try to use union all to split the case statement. But this will make the code look complicated.Or replace the case statement with or.

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Thank you Much pitauch.

I appreciate your valuable input. I am just checking is there any way that we can do with temp table of CODECHECKs

for example Create table #codecheck (CodeCheck varchar(4), CodeCheckDesc varchar(20))

insert into #codecheck values ('MILT','MILLER')
insert into #codecheck values ('ROMB','ROMAN BOWLER')
insert into #codecheck values ('BANK','LOANER')
insert into #codecheck values ('MORT','MOVABLES')

then use this table to join the main table above based on CODECHECK column and use the Codecheckdesc column in select clause?

Please advise your valuable inputs

Thanks Again
Asita

Check is this fits your need:

SELECT DISTINCT
 NDS = CASE
 WHEN TRIM(STG.CODECHECK) = 'MILT' THEN 'MILLER'
 WHEN TRIM(STG.CODECHECK) = 'ROMB' THEN 'ROMAN BOWLER'
 WHEN TRIM(STG.CODECHECK) = 'BANK' THEN 'LOANER'
 WHEN TRIM(STG.CODECHECK) = 'MORT' THEN 'MOVABLES'
 -- Add more condition as needed here for other cases
 END
 , TRIM(STG.OPTCODE) AS SRC_CD
 , UPPER(TRIM(STG.OPTCODEDESC)) AS DESCRIPTION
 , TRIM(STG.OPTCODE) AS CON_CODE
 , UPPER(TRIM(STG.OPTCODEDESC )) AS CON_CODE_DESC
 , 'HEALTHCARE' AS DOM_NAME
FROM MYCheck1Temp1 STG
WHERE STG.VALID_RECORD = 'Y' AND SRCCODE <> '' 
AND TRIM(STG.CODECHECK) in ('MILT', 'ROMB', 'BANK', 'MORT') -- add more option in the IN condition as needed

Note! VERY IMPORTANT! You should have the right indexes according to the filter conditions but using condition on TRIM(STG.CODECHECK) in the where part might reduce the performance dramatically since the server might not be able to use the index on STG.CODECHECK. It is better to store the data after it is trimmed if this fit your case.

Check this sample and why it is not a good idea to use filter on a column that you manipulate

create table T(id int identity(2,2), txt nvarchar(100))
insert T(txt) values (' ronen '),('ronen'),(' ronen')

CREATE CLUSTERED INDEX i1 ON T (id);
CREATE nonCLUSTERED INDEX i2 ON T (txt);

-- this scan all the index since the server must first execute TRIM on the value before it can filter by it
select txt from T where TRIM(txt) = 'ronen' 
-- this use the index seek which is very useful, since here the server could filter according to the index
select txt from T where txt = 'ronen'