The Lesser Used Functions

Question

The Lesser Used Functions

Steve Jones - SSC Editor

SSC Guru

Points: 736718
More actions
January 10, 2025 at 12:00 am

#4503308

Comments posted to this topic are about the item The Lesser Used Functions

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004457 More actions · Answer 1

I use IIF a whole lot to replace the longer CASE expression in CROSSTABs. It just makes for shorter code when there are only Yes/No conditions.

I have used some of the Bit-wise functions but only because someone else decided to store more than 64 conditions in a single column. That's horrible for doing searches. An EAV table might work better but that's awefully verbose, comparatively speaking.

I tested CHOOSE... it's slower than other methods.

I've not had the need to use TRANSLATE. It did do some minor testing with it just to make sure that it was behaving in the way I thought it would.

CONCAT_WS and TRIM were big winners from 2017 along with WITHIN GROUP for STRING_AGG and CSV file types for BULK INSERT (FINALLY!!!)

The real key is that, except for IIF (which came out long ago), I don't use any of the new functionality that came with 2022 because we had to change to the 1027 compatibility level because the massive performance hits caused 2022 killed us. The stuff that came out in 2017 was a big help though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23788 More actions · Answer 2

For single character swaps, like obfuscating numeric data, TRANSLATE() can be far friendlier than a load of nested REPLACE() functions.

Jeff Moden SSC Guru Points: 1004457 More actions · Answer 3

Just to be sure, I would never obfuscate numeric data. If it's worth protecting, it's worth being properly encrypted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Doctor Who 2 SSCrazy Eights Points: 8203 More actions · Answer 4

The IIF operator is one that I use. Honestly, that's more because I've used it in MS Access and VB.NET.

Rod

andrew.courneya 63072 Grasshopper Points: 16 More actions · Answer 5

I have used IIF if I think:

The arguments are not going to scale - if it becomes more than True/False, or the evaluation of the condition is complex in number or logic of the evaluations, I will build it with a CASE statement from the get-go
If I think there isn't another way to flip the argument that is more elegant and readable/maintainable
If it's not procedural code - if I can do it procedurally without an IIF and with something that is readable AS A WHOLE PART, I won't implement it

I looked at Choose, that one is interesting but I'd most definitely never use it. Ordinal referencing was trained out of me VERY early after some very uncomfy encounters with ordinal references that made me go "ew". That one seems to me to be more of a play for making legacy applications easier to move into SQL Server by standing up functions that use this heavily to obtain data out of an application or data stream. Which, at that point, in saying data stream, it almost seems like it's an add on to JSON capabilities and such in that space. Being I don't use that either, that's speculation, but it seems reasonable.

I'm going to review a few more of these. Interesting to say the least, but I'll almost always choose verbosity if it means clarity. Yes, I understand this notion will age like milk, but I think with "my time left" in tech - I think I can live my life by this principle for the remainder possibly. Hard to say how quick the transition will be with people who maintain code and having to interpret it with AI because it's been produced to be nearly unreadable by a human (the result of operations previously done with AI in that scenario). I think that's the day I'll quit tech, when I have to look at some sort of behemoth/monstrosity built by someone who didn't really know application development. Being that I've already encountered patterns like that in the wild without the AI component being a factor; I expect we'll see more of this. Hopefully functions like this that are well documented and performant, will find themselves into more frequently-suggested-use in our systems through the power of suggestive implementation.

Steve Jones - SSC Editor SSC Guru Points: 736718 More actions · Answer 6

Thanks and I tend to agree on Choose. It seems like a "magic hard coding" way of putting in values, which worries me. I prefer things to be more flexible than an index into an array.

I tend to agree with verbosity as well. Early in my career we'd always aim for conciseness and tight code, but these days, with larger teams, this means more time spent trying to understand (or less and misunderstanding) code, when time is more valuable than a little space.

Jeffrey Williams SSC Guru Points: 90321 More actions · Answer 7

Steve Jones - SSC Editor wrote:

Thanks and I tend to agree on Choose. It seems like a "magic hard coding" way of putting in values, which worries me. I prefer things to be more flexible than an index into an array.
I tend to agree with verbosity as well. Early in my career we'd always aim for conciseness and tight code, but these days, with larger teams, this means more time spent trying to understand (or less and misunderstanding) code, when time is more valuable than a little space.

If you really believe that about choose - then you really should not ever use a simple case expression.

CASE someColumn
     WHEN 1 THEN 'One'
     WHEN 2 THEN 'Two'
     WHEN 3 THEN 'Three'
 END

CHOOSE is just a shortcut to a simple case expression. With that said - I would not use CHOOSE unless I know the only available values are going to be a small set of incrementing values.

It is a lot less useful than IIF - but I have found it to be useful in a few situations.

I have one example where I chose to use choose 🙂

I have a calculation where I need to determine how many days to add based on the day of the week. There will only ever be 7 possible days - and depending on the day we will increment the date either 1, 2 or 3 days.

Forward = DATEADD(DAY, choose(DATEPART(weekday, t.DateColumn), 2, 1, 1, 1, 1, 3, 2), t.DateColumn)
Backward = DATEADD(DAY, -choose(DATEPART(weekday, t.DateColumn), 2, 3, 1, 1, 1, 1, 1), t.DateColumn)

This could be rewritten use CASE - or we could use a table and join based on the date part - or possibly other methods, but in this situation CHOOSE works well and allows for shorter and more concise code. YMMV.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 736718 More actions · Answer 8

That's a good example. I guess I don't often run into places where I know it's 1, 2, 3. Often it's matching some other value, or matching strings.