Do SQL Devs create SQL queries using SQL Query Designers

A generic database design limits the proliferation of new tables as the number of forms and data elements grows. On the other hand even the simplest query involved 3 tables, in our case.

Queries indeed got very mind boggling. After 3 years of this I became of the opinion that explicit tables was better. I'd rather deal with more tables that better reflect the business form/model - that I can understand just by looking at them; vice 1/2 the tables but with multitudes of meaningless join tables requiring astonishing long, nested queries to make even the most rudimentary sense out of the data. You actually start memorizing the meaning/context of individual identity column primary keys!

Finally, we had evolved to where we had that one table to rule them all so as to get away from some of the inherent complexities (and bad coding and design decisions of the past) in the existing design and evolving user requirements. Nonetheless, we were certain we'd have problems as the table size went into the million+ row size.

Dynamic SQL is used in situations where the conditions aren't known ahead of time. One example I've run into often is an "advanced" search form where the user can search on multiple fields and can search beyond simple "substring" searches where they can specify a search value must match exactly (OR match as a substring, and case sensitivity is an option), and for numeric/date values they can specify operators >, <, >=, <=. It might also happen that one search form is actually searching multiple joined tables. In this case, it's much easier to build a query on the fly as a string and execute it that way, than to try to imagine every possible query they user could submit and have if-else blocks choose a pre-existing query.

You are correct though. Dynamic SQL can be very difficult to debug (when debugging, I add code to print the query with and without the substitution values before executing it) and can also run slower.