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:

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.