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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-24 : 08:17:56
|
| ryan writes "Just wondering what will execute quicker in ms-sqlCOALESCE case if is nullSomething like the following:WHERE o.blnDeleted = 0and o2l.strOfficeName=COALESCE(@officename,o2l.strOfficeName)" |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-01-24 : 08:19:53
|
isnull is fastest.coalescse acctually translates into case'sGo with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-24 : 08:20:19
|
| From the example you gave, IF is the most appropriate performance wise and will generate much better plan than any other option.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-01-24 : 08:22:41
|
| he gave an example? where?Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-24 : 18:14:09
|
| IF @OfficeName IS NULL select ...ELSE select ... where o2l.strOfficeName = o2l.strOfficeNamePeter LarssonHelsingborg, Sweden |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-01-24 : 22:47:13
|
| Depends what you are doing. You can only ever test performance of things that are not the same against a specific case. See which one is fastest by trying each one and measuring it. If there was a hard & fast answer then one would assume that either the optimiser would simply use it all the time or the language designers would remove the options completely, no? |
|
|
|
|
|
|
COALESCE vs case vs if vs is null