sql server 2005 - Can Common Table expressions be used here for performance? -

can common table expressions used avoid having sql server perform following string parsing twice per record? guess "no."

select distinct     client_id     ,right('0000000' + right(client_id                              ,patindex('%[^0-9]%'                                        ,reverse('?' + client_id)) - 1)            ,7) correctedclient     membob_vw     client_id <> right('0000000' + right(client_id                                          ,patindex('%[^0-9]%'                                                    ,reverse('?' + client_id)) - 1)                        ,7) order      1     ,2  

every time try format sql "code block" looks (displaying on multiple lines) until page refreshed, after point sql displayed , me @ least, on 1 line- , can't seem corerct that.

does display way people using browser new ie6? company imposes pos browser on me , prevents me using other.

no, cte not performance wise query. may seem strange/inefficient type in same thing large string expression twice. however, sql server string expression 1 time per row, has been optimized things that.

edit
cte reduce duplicate code:

;with allrows ( select distinct     client_id     ,right('0000000' + right(client_id                              ,patindex('%[^0-9]%'                                        ,reverse('?' + client_id)) - 1)            ,7) correctedclient     membob_vw ) select * allrows client_id<>correctedclient order      1     ,2  

but won't perform better. use set showplan_all on , i'll bet see same query plan each version.

be careful trying make queries pretty , reduce redundant code fragments! simple looking sql changes can have major adverse performance implications! performance (run and/or query plan) check changes make. have seen trivial changes made queries run instantly, results in them taking minutes run. key sql performance not pretty code. if application slow, cares if code looks good.