Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sent_sara
Constraint Violating Yak Guru
377 Posts |
Posted - 2013-06-18 : 14:10:34
|
Can i know how to write the optimized way in select statement,avoiding the case statment.select CASE WHEN RACCT = '' OR RACCT IS NULL THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS AccountFrom DBO.GL |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 14:14:23
|
it can be simplified as below but internally its equivalent to CASE ...WHEN so cant say its optimized in performanceselect COALESCE('AC_' + NULLIF(RACCT,'') + '','No_Account') AS AccountFrom DBO.GL ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-18 : 15:49:02
|
When you run a query, most of the resources are spent on retrieving the data, sorting it etc. Very little resources are required for arithmetic/logical operations as in the CASE expression you are using. So I would keep it as it is. It is more readable as well, just format it nicelySELECT CASE WHEN RACCT = '' OR RACCT IS NULL THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS AccountFROM BO.GL |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-06-18 : 16:33:14
|
Is this any better? select CASE WHEN isnull(RACCT,'') = '' THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS AccountFrom DBO.GL |
|
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
Posted - 2013-06-18 : 17:22:59
|
Thanks everyone. i've implemented all the changes mentioned.but performance wise no variance.Thanks anywayquote: Originally posted by nevzab Is this any better? select CASE WHEN isnull(RACCT,'') = '' THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS AccountFrom DBO.GL
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 01:01:40
|
quote: Originally posted by nevzab Is this any better? select CASE WHEN isnull(RACCT,'') = '' THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS AccountFrom DBO.GL
still the same other than a small overhead may be due to use of function------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
Posted - 2013-06-19 : 01:15:36
|
hi visakh,I need one more help.can you have a look on the topic "query tuning"quote: Originally posted by visakh16
quote: Originally posted by nevzab Is this any better? select CASE WHEN isnull(RACCT,'') = '' THEN 'No_Account' ELSE 'AC_' + RACCT + '' END AS AccountFrom DBO.GL
still the same other than a small overhead may be due to use of function------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
|