Tag: T-SQL

  • Experimenting with CHOOSE: #SQLNewBlogger

    I was looking over the CHOOSE() function while editing an article at SQL Server Central. I realized there isn’t a documentation note on how many parameters can be included. I decided to test.

    This post looks at the tests and results.

    Tl;dr It appears 3925 is the most arguments.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    Building a Script

    Since I want to know where things fail, I decided to make a short T-SQL script that tests increasing numbers of parameters. This script builds a dynamic CHOOSE() select using a number of parameters and then loops for a set number of times, adding 1 parameter each time.

    SET NOCOUNT ON; DECLARE @MaxValues int = 5150; -- largest number of CHOOSE value arguments DECLARE @n int = 300; -- current number of value arguments DECLARE @i int; DECLARE @idx int; DECLARE @args nvarchar(max); DECLARE @sql nvarchar(max); WHILE @n <= @MaxValues BEGIN -- Build value list: 'Value 1', 'Value 2', ... 'Value n' SET @args = N''; SET @i = 1; WHILE @i <= @n BEGIN SET @args += CASE WHEN @i > 1 THEN N', ' ELSE N'' END + N'N''V' + CAST(@i AS nvarchar(10)) + N''''; SET @i += 1; END; -- Random index in 1..n (CHOOSE is 1-based; out of range returns NULL) SET @idx = ABS(CHECKSUM(NEWID())) % @n + 1; SET @sql = N'SELECT ' + CAST(@n AS nvarchar(10)) + N' AS ArgCount, ' + CAST(@idx AS nvarchar(10)) + N' AS PickedIndex, ' + N'CHOOSE(' + CAST(@idx AS nvarchar(10)) + N', ' + @args + N') AS Result;'; PRINT @sql; -- show the generated statement EXEC sys.sp_executesql @sql; -- run it SET @n += 1; END;


    This returns the command run. For the first 2 values of n, setting @n to 1 and @max to 2, I get this:

    SELECT 1 AS ArgCount, 1 AS PickedIndex, CHOOSE(1, N'V1') AS Result;
    SELECT 2 AS ArgCount, 1 AS PickedIndex, CHOOSE(1, N'V1', N'V2') AS Result;

    This let me test different values. I tried various values and eventually got this error:

    2026-09_0159

    I tried this a few different times, disconnecting and reconnecting and it seemed that I could get the number of parameters up to 3925. It failed sometimes at 3924 and sometimes at 3925.

    I’ve submitted a PR to add the max number to the docs, but it appears CHOOSE() works at a level that would cover lots of items. I see this as a small syntactic sugar add, and if I really needed to index 1000 entries, a join with a reference table would be a better choice.

    SQL New Blogger

    This script took a few minutes to write, but the testing took about 45 minutes. I was running this in the background with different values. 1..256, 255..1024, etc. I noticed that SSMS responded slower, so I started jumping to small ranges at large values to find the error.

    This post took me about 20 minutes to put together after the testing, which included the PR for Microsoft. This is an easy way to show that you can examine possibilities and find the limits of your code. That helps us decide how scalable or robust our solutions are.

    You could write this post and impress an interviewer. Choose your own T-SQL function and run an experiment.

  • The SUM of Nothing: #SQLNewBlogger

    I caught this interesting item over on Pinal Dave’s blog: Eleven Interview Questions that Look Too Easy. I decided to give you a few thoughts from me on the SUM one.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    The Sum of Nothing

    I am guessing more people working with SQL know that if you have a NULL in your data and try to sum the column, you get the NULL ignore. After all, you can’t sum up values if one is unknown. The code and query below show this:

    2026-09_0133

    Note, with ANSI_WARNINGS I get a note in the Messages tab:

    2026-09_0134

    However, what if there are no rows? Would you expect a 0, because if there isn’t any data, the sum is zero, correct?

    No.

    2026-09_0136

    Why? The docs don’t mention this (I’ve added a PR).  The ANSI standard notes that if all values are NULL or the set is empty, NULL is returned.

    Most of us don’t query empty tables, but we could get an empty set. Remember, the column list, and therefore aggregate, is evaluated after the WHERE and JOIN clauses. Therefore, as you see below, I could get a NULL in a sum where I expect data.

    2026-09_0135

    Make sure you account for this in your queries.

    SQL New Blogger

    I read reading another blog (Pinal’s) and realized this was interesting. I thought about if I’d have a problem and realized that I could because I’ve often assumed there is some data, but if I let users filter data from an app, I could return NULL.

    Easy to write, about 15 minutes to setup and do. You could do this.

  • Using the SIGN() Function: #SQLNewBlogger

    I was trolling the docs and noticed the SIGN() function. I have never written this in production code, but it is an interesting function. This post looks at where I might use this and when the need arises.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    How It Works

    The SIGN() function works by taking an argument and evaluating if the value is positive, negative, or zero. The example in the MSLearn Docs shows the values working in a few ways. I’m reproducing that here to look at how it works.

    2026-08_0196

    If I change this to work with float and strings, we get similar values.

    2026-08_0199

    Essentially, this implements this code:

    IF @value > 0 SELECT @value, 1
        IF @value = 0 SELECT @value, 0
        IF @value < 0 SELECT @value, –1

    Or this code:

        CASE WHEN @value > 0 THEN 1
        WHEN @value = 0 THEN 0
        WHEN @value < 0 THEN -1
        END AS valsign

    This is a simple function, and it’s easily duplicated in code, so why use it?

    Use Cases

    Most of the mathematical algorithms I’ve implemented don’t deal with negative numbers in a material way. Aggregates, such as averages and sums will take the value into account and the sign isn’t important.

    In some cases, it might. Perhaps I want to do some math around distances from zero, but I don’t want the values to cancel each other out. For example, maybe I have a small data set. I have some shipments and weights.

    2026-08_0207

    Now, it makes sense that we’re shipping to and from our warehouse and tracking the direction with a negative quantity for returns. However, to calculate total shipping weight, a sum doesn’t work:

    2026-08_0209

    I really want to normalize the values. I could use SIGN() here, as shown:

    2026-08_0210

    Of course, ABS() works as well, so that’s not necessarily a great example. I’d argue both are slightly obscure without a comment in the code.

    2026-08_0211

    Another example, perhaps I’m looking to determine a trend of movement. I saw this on the Internet from someone else.

    If I run this code, I’m getting the change of values, but also the direction of travel. That TrendDirection lets me know which ways things changed.

    2026-08_0202

    I might want to look for (or alert on) a trend. So, if I look at lines 14-17, I have a trend of increasingly negative values. Perhaps if I have 3 in a row (a complex LAG), I raise an alert.

    Here’s a LAG with SIGN repeated to show that.

    2026-08_0205

    There are other cases I might care about, but these come to mind.

    SQLNewBlogger

    This is an example of a post that shows I know how a function works, but mostly where I might use it. I added my own thoughts, and a couple of use cases.

    This post took about 40 minutes to write, with the code setup and some internet searching involved. I did use Prompt AI to generate some tables and code, which made things easy, but I had to think a bit on the scenarios and how I felt about them.

    All good things to showcase in the age of AI. If an AI generated code, could you determine the use? Knowing SIGN() can help. Write your own post and showcase your knowledge. Disclose if AI helps.

  • Why Use TRY_PARSE(): #SQLNewBlogger

    Someone asked why I would use TRY_PARSE after I posted a question at SQL Server Central: Getting the Average. Isn’t is slower?

    A fair question. This quick post looks at why.

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    A Quick Setup

    The question above has the setup code, but what if I add another row? For example, I’ll run this .

    insert dbo.commission
    (
        salesperson
      , commission
    )
    values
    (‘Steve’, ‘A’)

    Now, let’s look at the data and run the query from the question.

    2026-07_0371

    This works. However, let’s remove the (slow) TRY_PARSE() from the aggregate.

    2026-07_0372

    Error. Why? I can’t convert “A” in the AVG to a number. It fails.

    You might think, I’ll never get bad data like this. But you might? A user might enter something you don’t expect. An AI might model this as a string, which is bad, but it happens. If it’s an EAV type table, or there are other data  items and you’re trying to extract the numbers from here, TRY_PARSE is helpful.

    SQLNewBlogger

    I wrote a post last week and this is a followup that really just took less than 5 minutes to setup and run. Plus I responded for the user in the post.

    This showcases me thinking about a question and situation and really gives an interviewer something to ask me. This lets them dive into my thought process and gives them confidence I don’t just write code without thinking.

    Add to your blog with short posts like this (or drop on LinkedIn).