A Plus Puzzle

Have you ever wondered how many plus signs you can put in front of a number before SQL Server throws a fit?

SELECT +++++++++++1

You haven’t?! That’s probably because you have production issues to solve, and I was just bored one afternoon. But are you curious now? Add enough +s and you’ll eventually get an error saying

Some part of your SQL Statement is nested too deeply.

If you’re like me (nerdy, intrigued, and without any production issues to solve right away), you’ll probably use something similar to the binary search algorithm to find the most + signs possible. Lucky for you, I already wrote the algorithm in T-SQL

DECLARE @MaxSuccess int = 1, --lower bound for testing, largest known success
@MinFailure int = 2000, --upper bound for testing, smallest known failure
@NumToTest int = 500,
@sql nvarchar(max)

WHILE @MaxSuccess <> @NumToTest
BEGIN
  BEGIN TRY
    SET @sql = 'SET PARSEONLY ON SELECT '+REPLICATE('+',@NumToTest)+'1'
    EXEC sp_executesql @sql
    --code below only runs on success
    SET @MaxSuccess = @NumToTest
    SET @NumToTest = FLOOR((@NumToTest+@MinFailure)/2)
  END TRY

  BEGIN CATCH
    SET @MinFailure = @NumToTest
    SET @NumToTest = FLOOR((@MaxSuccess+@NumToTest)/2)
  END CATCH
END

SELECT @MaxSuccess AS 'Max success with PARSEONLY on'

Adding SET PARSEONLY to the query counts against the number of + signs. Add 1 to the final result as a true max if you’re mashing the + key N times.

I get 1015 + signs as a max in SQL Server 2016. Different versions got different results for me.

Bonus question: how many – signs can you use? Seriously, try it out by replacing the + with a – in the script. Then chuckle as you figure out what happened.