Slides from Data Platform Discovery Day and finding the limits of a WHERE clause

Yesterday, I was privileged to speak at the first Data Platform Discovery Day. This event was virtual and the brainchild of Data Platform MVP Matt Gordon (@sqlatspeed) with one day in US time zones and one for Europe. He realized that many folks will be looking to grow their marketable skills due to the uncertain economic times. The topics were all geared for beginners, and my session was an introduction to T-SQL (slides and code). Redgate also sponsored the event.

My session covered SELECT, FROM, WHERE, and ORDER BY. I learned something new during my session, too! Someone asked how many predicates can you have in a WHERE clause? My answer was that there probably is a limit, but that you should never reach it. Someone in the audience did give an answer, I think it was 4096. That turned out to be false, at least in the case of SQL Server. However, that is the limit on the number of expressions in the SELECT list.

The documentation says:

“There is no limit to the number of predicates that can be included in a search condition.”

So, the audience member probably found some information about a different database system.

After playing around with this, I did manage to reach a limit, at least where a query would no longer run. I created a script to run a query with a large number of predicates:

DECLARE @statement VARCHAR(MAX) = 'select name
from sys.databases
where 1 = 1 ';

SET @statement = ' select name from sys.databases where 1 = 1 ';
SET @statement = @statement +
   REPLICATE(CAST(' and 1=1' AS VARCHAR(MAX)), 9000);
SELECT LEN(@statement);

EXEC (@statement);

Somewhere between 8000 and 9000 predicates (depending on the server), I got this error:

Msg 8631, Level 17, State 1, Line 3

Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

I was hoping that I could increase the max memory settings and get more predicates to work but going from 4 GB to 32 GB didn’t make that much difference. I also wondered if it was the length of the statement or actually the number of predicates. When I switched to this query which has a much larger number of characters, it behaved the same:

DECLARE @statement VARCHAR(MAX) = 'select name
from sys.databases
where 1 = 1 ';

SET @statement = ' select name from sys.databases where 1000 = 1000 ';
SET @statement = @statement 
   + REPLICATE(CAST(' and 1000 = 1000' AS VARCHAR(MAX)), 8000);
SELECT LEN(@statement);
EXEC (@statement);

Ok, comparing a number to itself is quite simple. What if the expression were more complex? Nope, I got the same behavior when I ran this one:

DECLARE @statement VARCHAR(MAX) = 'select name
from sys.databases
where 1 = 1 ';

SET @statement = ' select name from sys.databases where 1000 = 1000 ';
SET @statement = @statement 
   + REPLICATE(CAST(' and 1 = case 
     when name = ''master'' then 1 else 0 end' AS VARCHAR(MAX)), 9000);

SELECT LEN(@statement);

EXEC (@statement);

What’s the moral of this story? Well, there are two: Matt and crew put on a first-class event for beginners, and I like breaking things.

About Kathi Kellenberger

I am the editor of the online journal Simple Talk. I love talking about SQL Server to anyone who will listen, just ask my seven year old granddaughter. I love to write and teach. I am so humbled and honored to say I am a Data Platform MVP, a volunteer with LaunchCode and co-leader of PASS Women in Technology Virtual Group.
This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s