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.

Posted in T-SQL | Leave a comment

Join Us for Redgate Streamed!

With many in-person events, including SQLBits and SQL Saturdays, cancelled for the next few weeks, it’s time to look at other ways to learn and keep up with the community. There’s a ton of recorded content online that you can watch, but that doesn’t help with connecting with the friends you’ve met at SQL Server events over the years.

To fill the gap, Redgate is hosting a free streamed event April 1st through 3rd with topics ranging from DevOps to blogging to Extended Events and featuring speakers like Grant Fritchey, Kendra Little, Steve Jones, Cathrine Wilhelmsen, Chris Unwin, and more! You’ll have a chance to interact with us and the community and learn something new along the way.

Redgate is making a donation to the World Health Organization Covid-19 relief fund in honor of the event and will increase that amount based on the number of registrations. Not only will you learn something new by attending, you will help others around the world! If you can’t join us for every live session, we are recording all the sessions that you can watch them later at your leisure.

If I haven’t convinced you yet, find out what Grant Fritchey, Kendra Little, and Annabelle Bradford have to say about the event.


Posted in Life in IT | Leave a comment

SSRS Azure VM Checklist

Recently I set up an Azure VM running SSRS for my students to host their database and report projects. My goal was to set up a custom domain name and use SSL. I ran into a few issues and a couple of wrong paths so I thought this would be helpful for others trying to accomplish the same. I’m not going to do a step-by-step walkthrough, but instead a checklist to help you troubleshoot

1.      Create a VM in Azure.

In this case, I selected the Standard D2s v3 image running Windows 10 Pro. You might want to run a bigger machine or switch to Windows Server. I selected this particular configuration to save costs while also having enough resources to perform well enough for these tiny databases and reports.

When creating the VM, you may want to create a new resource group. Add any other objects associated with the VM to the resource group. That way, if you want to delete the VM, you delete the resource group so that all objects are cleaned up.

2.      VM Configuration

In order to use a custom domain, make sure that the IP address is Static. You can find this setting on the VM’s IP properties.


You need to make sure that the Network Security Group allows traffic on the ports. Click the Networking settings of the VM and select “Add inbound port rule.” You should allow 443 for SSL, but you may also want to open 80 for testing to make sure you can connect to SSRS without SSL first.


Make sure that you also open the Firewall settings inside the VM to allow the ports. This is where I messed up. I had forgotten to open 443.

3.      Software Installation

This list is simple:

  • SQL Server 2019 Developer Edition (Install the Database engine only unless you need additional features)
  • SQL Server Reporting Services
  • SSMS or Azure Data Studio

Configure SSRS as you would in any other situation. You may need to specifically grant administrator rights to your account in the SSRS web portal while remoting the VM. Run the browser as an administrator to do this. Note that if these are production reports instead of for class projects, you’ll need a different version of SQL Server.

4.      Domain Name

I purchased a new domain name from which took just a few minutes, and it’s less than $20 for one year. To get this to work, I created an Azure DNS Zone. This ties the domain name to the IP address. I followed instructions here and here. I also set up the A and CNAME records in the DNS properties on GoDaddy. For whichever provider you use, you should be able to find some help on their site for managing sites hosted by other companies.

5.      SSL Certificate

I also purchased my SSL certificate from GoDaddy. I used to deal with domain names and SSL certificates 20 years ago, but this took me a few times to get right.

After purchasing, you must generate a certificate signing request (CSR) on your server that is then submit it to the provider. I followed the instructions here to create the CSR. On GoDaddy, there are some instructions found here on requesting the SSL certificate. In this case, follow the steps found at “Request a certificate with a CSR that has already been created.” Using a CSR generated by GoDaddy was one of the mistakes I made.  SSRS could never see the certificate in that case.

If you buy yours from another provider, they should have some instructions. Just make sure that the CSR is generate on the server, not by the provider.

6.      Installing the Cert on SSRS

I’m having trouble finding the resource I used, but you will need to import the certificate from your provider using the MMC snap-in for Certificates. Right-click the Personal folder –> All Tasks –> Import which will launch a wizard. You’ll import the CRT file.


Now that the certificate is installed, it must be set up on SSRS but restart SSRS first. I used this article to help me configure the certificate on SSRS.

Once that is done, you should be able to connect to your Web Portal with the new domain name and using https.


It all seems so simple now! While this doesn’t give step-by-step instructions, I hope it is helpful for anyone else trying to accomplish the same task.

Posted in SSRS | Leave a comment