Author: way0utwest

  • 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.

  • A Fun Prize at the PASS Data Community Summit

    I had a briefing for the 2026 PASS Data Community Summit, which is just a couple of weeks away. Amongst a bunch of admin type stuff, there was a slide with a picture of the Redgate Software prize at our booth.

    It’s a Lego Concorde model. That is very cool.

    2026-10_0144

    If you’re attending the Summit, swing by and enter for your chance to win. It’s a pricey (to me) set, though I might have to get one and put a shelf behind my desk with it, and a few other things I need to get on display.

  • Making an Impression

    It’s a weird time in the job market. Companies are hiring, and I see friends and colleagues starting new positions. At the same time, I have had many more friends reach out, saying they are struggling to find employment, and job searches are dragging on for months. I sympathize, and it certainly seems that lots of people are trying to hold onto their existing positions.

    Whether you’re looking for a new position or trying to create security in your existing role, it pays to be aware of the impression you leave in the world. Is it a positive one? Do others want to work with you? Or maybe you find people gravitate towards others. Are they impressed with your output? Satisfied? Left wanting?

    Maybe a better question is: if you were running your team, would you hire you again?

    I think it’s a very human tendency to overestimate our abilities and underestimate our weaknesses. That’s why 90+% of people rate themselves as above-average drivers. In my travels, I find plenty of people who aren’t very self-aware of the impression they make, good or bad. Lots of people aren’t very observant, which is another human tendency.

    Recently, a friend was hiring for a position and noted that they received a large number of resumes and cover letters with spelling and grammar mistakes. They deleted those and then started to review the rest. They also mentioned that while checking LinkedIn, they were turned off by people who hadn’t added their picture. I’m not sure if those candidates were discarded.

    Whether you think those are valid reasons to judge others, the reality of the world is that humans judge each other. We have our own prejudices, attractions, turn-offs, and more that influence our decisions. It’s easy to say that you’ll pick the best candidate, but that’s much harder in practice. Many of us fall into buckets with lots of others who are applying. How do we stand out?

    It can be hard, especially when different people form different impressions from the same experience. Not enough experience is a challenge, and these days too much experience can also be a challenge. There are plenty of hiring managers who might not consider a candidate if their impression of a candidate is “too old”, “too expensive”, or “too set in their ways.”

    It’s a tough world these days, and AI doesn’t help. What I can say is that you should be aware of the impression you make and work to regularly improve it. It’s unlikely you’re clearly above or below others who have similar experiences and skills, but building a preponderance of evidence that you are a good hire might make a difference.

    Steve Jones

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

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

  • Monday Monitor Tips: Query Recommendations

    We keep adding stuff to Redgate Monitor, and it’s easy to miss some of the improvements. Here’s one that’s been around quite awhile, but while discussing the product with a customer who uses Redgate Monitor, I realized they weren’t aware of this.

    This is part of a series of posts on Redgate Monitor. Click to see the other posts.

    The Blue Blob

    I was looking at a set of queries on the SSC servers with an SE and a customer. The SE pointed out that we surface some code issues on this page. There’s a blue lightbulb next to one of the queries.

    2026-06_line0007

    I’ve zoomed in, but if you go to monitor.red-gate.com, you’ll find this somewhere in the list. This is to the right, where the database name is listed.

    2026-06_line0006

    If we expand this query from the left side (the arrow), we see details, but below the plan handle, we’ve noted that Redgate Monitor has run code analysis and identified an issue. In this case, there’s one issue, but I’ve seen 3 or 4 on some client systems.

    2026-06_line0008We can click the blue text above (1 issues with this query) and this opens a dialog where I can see the query and the code analysis issue identified. In this case, we don’t have a schema name in here, which is just poor practice and a potential place you could have issues, if you add schemas and users might have a different default.

    2026-06_line0009

    One cool thing, is that if I click the slide in the upper right, I get a better format for the code.

    2026-09_0164

    Our code analysis rules are documented on our side, and there is a link at the bottom of the dialog that popped up. It’s to the lower right, and I cropped it out to get a slightly better image. If does to the Code Analysis pages at documentation.red-gate.com. Here you can read about our code smells and rules, which are also checked by SQL Prompt.

    Summary

    This is a small thing, but it helps with Operations people who might want to suggest ways that code could be improved, whether to clean up the codebase or for better performance. Once you start to establish guidance, and provide useful solutions, you can collaborate and other changes are possible.

    If you use Redgate Monitor, look for these items and see if you can encourage some refactoring from developers.

    Redgate Monitor is a world-class monitoring solution for your database estate. Download a trial today and see how it can help you manage your estate more efficiently.