"The Catch-All Report Query"

1. What is the primary issue caused by the catch-all pattern in SQL queries?

It requires excessive disk space. It leads to suboptimal query plans due to lack of specificity. It prevents the use of dynamic SQL entirely. It enforces stricter data types for parameters.

2. Which fix is recommended for reports that run a few dozen times a day and experience performance issues due to the catch-all pattern?

Dynamic SQL with 'sp_executesql'. Branching into separate statements. Option (Recompile). Using ISNULL functions to handle NULL parameters.

3. What is one consequence of using the OR conditions with the catch-all pattern in WHERE clauses?

Improved cardinality estimates. Increased SARGability for the supplied parameters. Compiling a plan that utilizes all available indexes. Decreased SARGability due to scan operations.

4. When implementing dynamic SQL to address the performance issue, what is a crucial practice to avoid SQL injection?

Concatenating user input directly into the SQL string. Validating input against a whitelist for object names only. Using EXECUTE AS to elevate permissions. Applying complex algorithms to user input.

Continue: "Check Your Server Version First" → Review this lesson Retake quiz