The Lesser Used Functions

Voice of the DBA

Recently, I reviewed an article that examined the bitwise functions that were added to T-SQL in SQL Server 2022. As I was looking over the article, I started to wonder if anyone was using these in production code. I used to do bitwise work early in my programming career, when memory and space were tight. However, it always felt like I was hiding some information that a subsequent developer (or my future self), might easily miss.

I looked through some other changes to the T-SQL language in the last few versions and made a list. This week I wonder if any of you use these functions in production code?

• CHOOSE
• IIF
• GET_BIT
• SET_BIT
• BIT_COUNT
• DATE_BUCKET
• APPROX_PERCENTILE_CONT
• APPROX_PERCENTILE_DISC
• LEFT_SHIFT
• RIGHT_SHIFT
• BIT_COUNT
• GET_BIT
• SET_BIT
• TRANSLATE

There are some interesting changes in here, and I can see the use for these functions, but I suspect these are specialized functions built for specific situations (or customers). I don’t expect many people to use them outside of those situations, but maybe I’m wrong. Perhaps some of you like doing bitwise operations, you like the logical CHOOSE/IIF, or maybe you can stomach approximate calculations.

I’m glad that the T-SQL language continues to grow. I would like to see more changes that ease the development of database code, though I would like Microsoft to ensure these new functions perform well. Some of the changes added in the past haven’t done so, especially when a dotNet function is wrapped with T-SQL. Performance matters and many of these functions will be called in queries that need to compete a result with thousands of rows.

Let us know today if you’ve found these functions useful in your work, or if there are changes made in the past that you would like to see improved.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.