These are not the same! The
case expression returns one type and in this case you want the type to be a string (because
'5+' is a string). However, mixing strings and integers in the
wheres will result in a type conversion error.
Which is faster depends on the distribution of the data. If most of the data consists of 5 or more, then the second method would be faster . . . and work if written as:
(CASE WHEN ClaimNo.ClaimNo >= 5 THEN '5+'
ELSE CAST(COALESCE(ClaimNo.ClaimNo, 0) as VARCHAR(255))
END) as ClaimNo,
In fact, there is only one comparison, so from the perspective of doing the comparisons it will be faster.
The next question is whether the conversion from a number to a string is faster than the multiple comparisons with each value listed separately. Let me be honest: I do not know. And I have been concerned about query performance for a long time.
Why don't I know? Such micro-optimizations generally have basically no impact in the real world. You should use the version of the logic that works; readability and maintainability are also important. Of course performance is an issue, but the bit fiddling techniques that are important in other languages often have no place in SQL which is designed to handle much larger quantities of data, spread across multiple processors and disks.